Link to home
Start Free TrialLog in
Avatar of RishiSingh05
RishiSingh05Flag for United States of America

asked on

Conditional Formatting

I need to build conditional formatting in the attached file.  Here is the logic:

Where B2 is blank (has no value):
If C2 is greater than E2  … make C2 green
If C2 is equal to E2  … make C2 yellow
If C2 is less than E2  … make C2 purple

Where B2 has a value it will always override C2:
If B2 is greater than E2  … make B2 green
If B2 is equal to E2  … make B2 yellow
If B2 is less than E2  … make B2 purple

Avatar of akitsupport
Flag of United Kingdom of Great Britain and Northern Ireland image

Nice little tutorial here.

The three colour scale might work for your best option.  But your going to have to set this on every cell that you want it on as you are using different values each time.

Your going to need to use several options here like it will need to ignore a condition if as you have said B2 has a value.

Looking at the options and my knowledge of it I don't see that its possible for it to do everything you want.
Avatar of RishiSingh05


Thanks. There are some experts here who might be able to craft a formula.
Avatar of Rory Archibald
Do you wish to apply it to all rows based on the value in E on the same row? If so, what if E is blank?
Yes to your first question.

If E is blank consider it a zero.  So, for instance, B2 (if it has a value) will be greater; or if B2 is blank then C2 (if it has a value) will be greater.
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
rorya, some guidance needed.  Where do I put those formulas?
Select data in col B then Home tab, Conditional formatting, New Rule, then select 'Use a formula to determine...' enter the first formula and choose your formatting, repeat for the other 2. Then repeat steps for column C.
Attached is demo file.
In your file:
C2 is correctly color coded
B3 should be green because it over-rides C3 and is greater than E3
B4 is correctly color coded
B5 and B6: everything here is zero so no color coding should apply
B7 is correctly color coded

Also, how do I view the underlying formulae?  
E3 is not actually blank. If you select it and press Delete, then you will see B3 coloured properly.
B5 and B6 have no colours.

To view the formulas select the cells then open the Conditional Formatting dialog as I described earlier and use the Manage rules option.
Thanks for the explanation.  I am attaching my file.  Pls take a look at rows 16 and 17.  It seems that there is a value in E16 and E17 greater than 60?