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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.