0

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

Community
  • 1
  • 1
user1086159
  • 1,045
  • 5
  • 16
  • 24
  • 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

3 Answers3

2

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:

=MONTH($E1) = MONTH(NOW())

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

Hope this helps.

John Bustos
  • 19,036
  • 17
  • 89
  • 151
2

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

=LOWER(A$1)= LOWER(TEXT(NOW(),"MMM"))

Note that the conditional formatting with override your row colors.

Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
1

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.

MCW
  • 81
  • 8
  • 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. – MCW Mar 13 '13 at 15:47
  • Thanks for all your help with this. All suggestions much appreciated. – user1086159 Mar 13 '13 at 15:49