Excel 2010 - Conditional Formatting

Question

I have a spreadsheet that I would like columns that relate to Saturday and Sunday to automatically colour fill. As the months and years change I would like for the formatting to recognise the weekends and fill the columns accordingly.

Answer 1

Kunal, I take it that the dates/weekdaynames come from the date-number in row 1, which you have formatted to show the weekday. In that situation, I would select e.g. the first column (all cells are highlighted), then select conditional formatting, new rule, Use formula to determine which cells to format and put the following formula in:

    =WEEKDAY(A$1,2)>5

and select the background color. Make sure the first column is selected and use format painter to copy the conditional formats to the other columns. What this does is look at the date number in row 1 and calculate the weekday number where Monday = 1 up to Sunday being 7. Any value above 5 is therefore Saturday or Sunday.

Answer 2

In excel 2010

• Select column that you want to format
• Click the Home tab.
• In the Styles group, click Conditional Formatting
• Select ' new rule '
• Select a rule type = Format only cells that contains
• In format Only cell with,set cell value to 'equal to' Sunday

• Click on format button and select Fill tab

• Repeat above steps for Saturday also

  If you want to do changes in this rule afterwords, click Conditional Formatting and select ' Manage Rules '

  Hope this will help you..