Conditional Formatting on a multiple Columns

[yhammerm]

Hi - I have a spreadsheet with 150 columns across- in this particular section there are 5 rows I am specifically interested in highlighting using conditional formatting.
There are 4 colors I am interested in conditionally selecting for these columns based on a specific value ( lets say, 1= Green,2= Red, 3= Blue, 4 = yellow).

So if my data looks like this :
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Plan[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cost[/TD]
[TD]$10[/TD]
[TD]$10[/TD]
[TD]$20[/TD]
[TD]$20[/TD]
[TD]$30[/TD]
[TD]$30[/TD]
[TD]$40[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Customer[/TD]
[TD]xyz[/TD]
[TD]abc[/TD]
[TD]212-t[/TD]
[TD]abc1[/TD]
[TD]abc3[/TD]
[TD]abc5[/TD]
[TD]abc6[/TD]
[/TR]
</tbody>[/TABLE]

I would like all columns where plan = 1 to be GREEN
I would like all columns where plan = 2 to be RED
I would like all columns where plan = 3 to be Blue
and I would like all columns where plan = 4 to be Yellow

These plans change so today column A might be in Plan 1 but tomorrow column A might be in Plan 3 depending on some other condition which determines who belongs in which plan.

I know that I can create 4 conditional formats per COLUMN ( "User formula to determine which cells to format") but given I have 150 columns ( each column represents a month for a number of years), that would be 600 rules, so I'm sure there is a better way to do this.

Can someone assist me?
How do I highlight columns B & C above in Green ....E & F in RED etc...
Your help would be greatly appreciated.

(ps - its my first post on this bulletin board, so forgive me if I haven't followed protocol).

 

[dave3009]

Hi and Welcome

Ideally you would have posted your version of Excel! There are differences between them all the most significant change happened between 2003 and 2007. I will provide a solution for 2007 as that is what I use.

Highlight all your columns, go into conditional formatting dialogue box and add a new rule, choose 'use a formula to determine cell format'. Now use =B$1=1 and set desired format for plan 1. Repeat for all plans.

Now apply and the columns will take on the desired format.