I have data file that I want to conditional format Red/Green fill based on =
conditional on a single row of data. In Excel 2003 it was fairly simple to
lock a row but not a column and by selecting all the cells to format it would
change the column relative to the cell. Just having an issue transitioning
to conditional formatting in 2007.


Sample Data
a b c d
1 goal 10 245 125
2 day1 8 200 76
3 day2 8 250 125
4 day3 15 250 130
5 day4 15 300 150
6 day5 0 100 0
7 Avg 9 220 96

Cells Rows 2 - 7 should be conditional on Row 1 value in the same column
Desired rows to be Red - b4,b5,c3,c4,c5,d3,d4,d5
all others should be Green

I actually have about 500 rows that I want conditional formatted on row 1 so
doing this with a block of cells selected is desired.

Thanks

Got it!
First the green fill. Can't you fill those a nice shade of green by default
rather than format?
The red thing now. It's really simple but had me going for a while.
In B2
Select Conditional formatting < new rule < use formula . . < in format
values etc input
=b2=b$1
format < fill red < ok ok etc

You can now use format painter to extend the formatting to the rest of your
range.


--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"stickandrock" wrote:

I have data file that I want to conditional format Red/Green fill based on =
conditional on a single row of data. In Excel 2003 it was fairly simple to
lock a row but not a column and by selecting all the cells to format it would
change the column relative to the cell. Just having an issue transitioning
to conditional formatting in 2007.


Sample Data
a b c d
1 goal 10 245 125
2 day1 8 200 76
3 day2 8 250 125
4 day3 15 250 130
5 day4 15 300 150
6 day5 0 100 0
7 Avg 9 220 96

Cells Rows 2 - 7 should be conditional on Row 1 value in the same column
Desired rows to be Red - b4,b5,c3,c4,c5,d3,d4,d5
all others should be Green

I actually have about 500 rows that I want conditional formatted on row 1 so
doing this with a block of cells selected is desired.

Thanks