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

Conditional-Format.xls

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

Conditional-Format.xls

Microsoft Excel

Thanks. There are some experts here who might be able to craft a formula.

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.

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.

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

or

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?

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.

Conditional-Format--1-.xls

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?

Thanks.

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?

Thanks.

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.

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?

CF3.xls

Your help has saved me hundreds of hours of internet surfing.

fblack61

http://www.homeandlearn.co.uk/excel2007/excel2007s6p2.html

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.