We help IT Professionals succeed at work.

How do I make the rows turn a different color using Conditional Formating in Excel?

Hi...how do I add the following conditional formatting to this Opportunity for Improvement (OFI) Tracker.
1. Row changes to 'red' if variance (column K) is below the tool cost
2. Row changes to 'yellow' if variance (column K) is below 25% of tool cost
3. Row changes to 'green' if variance (column K) is above 25% of the tool cost

I'm looking to see if we need to rework/repair a tool, are we still making money based on how much we charged the customer. Hope I explained this correctly.
Tooling-OFI-Tracker.xlsm
Comment
Watch Question

Test sample. I'm not sure in your conditions. Can you add more sample data? I think your first rule will never be true, because data will be always below or above 25% of the tool cost
Tooling-OFI-Tracker.xlsm
Marcia MorrisSr. Project Manager

Author

Commented:
als315 I've added more sample data.

If the variance is a negative then wouldn't it be true as it's less than the tool cost?

1. Row changes to 'red' if variance (column K) is below the tool cost
2. Row changes to 'yellow' if variance (column K) is below 25% of tool cost
3. Row changes to 'green' if variance (column K) is above 25% of the tool cost
Marcia: I don't see new sample.
Will your variance always be negative (I've added ABS function to eliminate negative numbers)?
Can you show expected colors for following values:
Cost      Price     Variance    %
800         500        -300         37.5    (Green? >= 25% or Red? < 100%)
800         600        -200          25      (Green? >= 25% or Red? < 100%)
...
Tooling-OFI-Tracker.xlsm
Marcia MorrisSr. Project Manager

Author

Commented:
als315, Opps....I forgot to attach.

The variance won't always be a negative (we don't want it to be) and the reason why we are needing to track this is because we spent 750K last year on tool rework/repairs because the cost exceeded the selling price (wasn't priced correctly to begin with).
Tooling-OFI-TrackerVer1.xlsm
Can you also show expected colors for all rows?
May be rules would be:
1. Red if variance <= 0
2. Yellow if variance > 0 and < 25% from cost
3. Green if variance > 0 and >= 25% from cost
Tooling-OFI-TrackerVer1.xlsm
Marcia MorrisSr. Project Manager

Author

Commented:
als315 this is exactly what I'm looking for. THANK YOU!

What would I need to do if I wanted the entire 'row' to change color vs just that column?
Rob HensonFinance Analyst
Commented:
To change the colour of the whole row, you have to apply the Conditional Formatting to the whole row.

In the Condition Formatting window, you will see the Formula, the Format and then "Applies to" which shows the range for each Condition. Amend the range in this input so that the required range is selected.

Also, as it is formula driven, make sure the reference column in the formula is absolute, ie $K

Thanks
Rob
Sample with whole row colored
Tooling-OFI-TrackerVer1.xlsm
Marcia MorrisSr. Project Manager

Author

Commented:
Thank you both!