Avatar of RishiSingh05
Flag 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

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

Thanks. There are some experts here who might be able to craft a formula.
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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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.
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

rorya, some guidance needed.  Where do I put those formulas?
Rory Archibald

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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?  
Rory Archibald

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?
Your help has saved me hundreds of hours of internet surfing.