Conditional Format cells based on a value within the row

[ddander54]

Is there a formula I can use in Conditional Formatting to highlight specific cells (not the row) based on the text in one cell in the row to highlight the data in the same row? Currently I'm creating 3 CF's (">0", "=0', "<0") in the specific row of data, then using the Format Painter to copy the format to the next row where the 'condition' occurs. There are now a thousand rows, so the Format Painter is a little too much on a daily basis.

My 'condition' is in Column D (starts at D8 is the text "Available Capacity"), My data to highlight is in Columns F:Q (F8:Q8)...Green fill if ">0", Yellow fill if "=0", Red fill if "<0".
So my data looks like this:
Col C | Col D | Col F | Col G | Col H.......
John | blank | blank | blank | blank.....
John | Total Capacity | 132 | 148 | 156.....
John | Total Assignments | 100 | 152 | 156
John | Available Capacity | 32 | -4 | 0
Suzy | blank | blank | bank | blank
Suzy | Total Capacity | 148 | 150 | 132
Suzy | Total Assignments | 148 | 148 | 152
Suzy | Available Capacity | 0 | 2 | -20

Or is this just a job for VBA?


Thanks,
Don

 

[Joe4]

Select the whole range that you want to apply it to, then write the formula as it applies to the very first cell in your selection.
Provided that you use the proper mix of absolute/relative range references, Excel will automatically adjust the formula for all the other cells (just like when you copy a formula down or across).

 

[Fluff]

Highlight the data you want to work on, say F2:H10 & use this formula
=AND($D2="Available Capacity",F2<0) for the red

 

[ddander54]

Joe4, Fluff,

For some reason when I previously tried the =AND.... formula I wasn't able to get it to work. It works like it should now.

Thank you for your confirmation and answer!

Don

 

[Fluff]

Glad we could help & thanks for the feedback