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
Marcia MorrisProject ManagerAsked:
Who is Participating?
 
als315Commented:
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
0
 
als315Commented:
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
0
 
Marcia MorrisProject ManagerAuthor 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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
als315Commented:
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
0
 
Marcia MorrisProject ManagerAuthor 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
0
 
als315Commented:
Can you also show expected colors for all rows?
0
 
Marcia MorrisProject ManagerAuthor 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?
0
 
Rob HensonFinance AnalystCommented:
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
0
 
als315Commented:
Sample with whole row colored
Tooling-OFI-TrackerVer1.xlsm
0
 
Marcia MorrisProject ManagerAuthor Commented:
Thank you both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.