RishiSingh05

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

ASKER

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?

ASKER

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.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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.

Conditional-Format--1-.xls

ASKER

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.

ASKER

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

CF3.xls

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.