Conditional formatting based on column header

[bpt]

I have a number of columns and I want to colour them either Green or brown based on whether the text in A1:a10 is "Actual" or "Budget". Is there a formula that can apply to the whole cell range or do I need to do each column separately? I am using Excel 2007.

 

[Jerry Sullivan]

Did you mean A1:J1 for your headers?

You can apply a formula to the whole cell range; however you will probably need two rules (Budget, Actual). In this example the formulas are applied to the range B3:Z100 just to show you don't have to do all rows in the column.

Excel Workbook
	A	B	C	D	E
1		Budget	Actual	Budget	Actual
2		Sales	Sales	Cost	Cost
3	Jan-11	149	178	178	126
4	Feb-11	180	176	107	125
5	Mar-11	116	169	143	149
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3	1. / Formula is =B$1="Budget"	Abc
B3	2. / Formula is =B$1="Actual"	Abc

Be careful to use B$1 and not $B$1 so that formulas will be relative to each column.

 

[SteveOranjin]

Hello.

I am having the same problem, except I want to say.

IF HEADER="MSRP" THEN SET TO NUMBER (FORMAT).
The data itself changes, so the position it is in in the sheet changes. So I can't say, "Column F" or anything like that.

Steve