Take the 2-minute tour ×

I have a spreadsheet that has various rows filled in colours while the columns are set to months in text form - 'mmm'

what I want to do is format the sheet so the column that is the current month is shaded - for example - March - to show it is the current month. This will change as we move into April etc etc

I only want Current Month column to be shaded but I need to keep my original 'filled rows' as shown in the below screen example as they highlight other important info.

example sheet:-enter image description here

Can anyone point me in the correct direction?

All advice very welcomed.

I am using Excel 2011 for Mac.

my hoped for result is something along the lines of the below:

enter image description here

share|improve this question
Can you us a screen shot of your expected results? (how your sheet should look like after shading) –  bonCodigo Mar 13 '13 at 14:52
what have you tried? I would suggest using some VBA in the Workbook_Open event to loop through the column headers and once you find a match on the current month, place a border around the column. That way you don't lose the other column formatting, but you can still highlight the column for the current month. –  Scott Holtzman Mar 13 '13 at 15:01
I tried conditional formatting but can't seem to get it to work how I wanted? –  user1086159 Mar 13 '13 at 15:08
add comment

3 Answers

up vote 2 down vote accepted

Here's another Conditional Formatting formula that works with your setup. Add this formatting rule from A2 to L22 in your case:


Note that the conditional formatting with override your row colors.

share|improve this answer
add comment

You can do this very easily with conditional formatting.

Suppose all your dates are in column E, you could do a conditional formatting rule such as:


Set it to all the data you want highlighted and then just choose the fill color you want.

Hope this helps.

share|improve this answer
nice non-VBA solution! –  Scott Holtzman Mar 13 '13 at 15:06
add comment

If you insert a second row (it can be hidden) with month number (A2 = 1, B2=2, etc) then you can apply the following conditional format

=month(now())=A$2 and then apply formatting as you like.

You need to make sure that this applies to the entire range.

share|improve this answer
that gives me more or less what I want. Will this change automatically when the spreadheet is opened in April? –  user1086159 Mar 13 '13 at 15:18
Yes; the value of (month(now()) will change to 4. I should note that I think John Busos' solution is more elegant; it doesn't require my month number hack. –  Mark C. Wallace Mar 13 '13 at 15:47
Thanks for all your help with this. All suggestions much appreciated. –  user1086159 Mar 13 '13 at 15:49
add comment

Your Answer


