Thread: Is it possible to conditional format an entire column based on what is in one cell?

Hi there,

I currently have a problem with a spreadsheet I use at work, each month the spreadsheet is refreshed and it uses Microsoft Query to extract information from a program we use throughout the company. The columns display figures for each month starting from 24months previous to the current month. With this the columns are then coloured coded by quarter, but this is currently done manually and to put it in simple terms Excel doesnt like it!! I think there is too much information and it frequently stops responding.

Ideally I would like it to update the spreadsheet automatically, with columns being formatted based on the top cell which will have a month in. For example,

April, May, June - Columns coloured Blue
July, August, September - Red
October, November, December - Yellow
January, February, March - Green


I would appreciate any ideas on how this could be done, whether I use conditional formatting or anything else.

Thanks in advance,

Kyle Ward.

If you're using 2007, you can set the conditional formatting to "Use a formula to determine which cells to format", and "format the values where this formula is true:" to

HTML Code: 

=(OR($A$1="April",$A$1="May",$A$1="June",$A$1="July"))

then select the Fill color to whatever you wish. (Change the $A$1 to whatever your column's first cell is, obviously.)

Change the "Applies to" field to =$A:$A (or whatever column you're in.)

Use multiple rules, changing the months and formats, to cover all your bases.

There's likely a more elegant way, but this is what would work for me.

How are the month headers generated?

Are they simple strings, or strings resulting from a date that has been formatted "mmm"?
You might need to consider something on these lines, or equal, if they are date derived

=OR(TEXT(A$1,"mmm")="Apr",TEXT(A$1,"mmm")="May",TEXT(A$1,"mmm")="Jun")

...etc