Conditional Formatting highlighting cell incorrectly

[weeksy]

Hi all

I am working on a table which needs a lot of conditional formatting so that the users know which cells they must complete. I'm mostly using highlight rules to make a cell turn red if they must enter text and the cell is currently blank but have other formatting relating to case on the text they enter etc.

I have one cell which is displaying a colour though the terms of the conditional formatting has not been met. I have 3 formulas in place and I have them in the below order as this represents the logic I'm using. (i.e D45 is dependent on D41. If D41 is blank then the user doesn't need to complete D45. If D41 is not blank then D45 should turn red to advise the user they must enter text. If they type in capitals, which is not allowed, the cell should highlight yellow. If they type in anything other than caps the cell should be white).

1) Cell D45 should be white if cell D41 is blank. I'm using =(ISBLANK(D41))
2) Cell D45 should be red if cell D41 is not blank. I'm using =NOT(ISBLANK(D41))
3) Cell D45 should be yellow if text in cell D45 is in upper case. I'm using =EXACT(D45,UPPER(D45))

All are set to "Applies to =$D$45".If I place the rules in order 1, 2, 3 then the cell is correctly white when D41 is blank, and red when there is text. But typing in CAPS into cell D45 leaves the cell red. This is wrong, it should be yellow.
If I place the rules in order 1, 3, 2 then the cell is correctly white when D41 is blank but is yellow when it contains text which is wrong. Cell turns red if lowercase text is used which is wrong.
If I place the rules in order 2, 1, 3 then the cell is correctly white when D41 is blank, and red when there is text. Typing in caps leaves cell red. Typing in lowercase leaves cell red.
If I place the rules in order 2, 3, 1 then the cell is yellow when D41 is blank, and red when there is text. Typing in caps leaves cell red. Typing in lowercase leaves cell red.
If I place the rules in order 3, 1, 2 then the cell is yellow when D41 is either blank or D45 contains upper case text. Cell turns red if lowercase text is used.
If I place the rules in order 3, 2, 1 then the cell is yellow when D41 is blank or contains text. Typing in Caps leaves it yellow and if lowercase text is used the cell turns red.

I can't seem to find a combination which follows the logic I'm after. Any help would be much appreciated.

I'm using Windows 7 and Excel 2013.

Thanks

Hilary

 

[Marcelo Branco]

I think you need just 2 rules

Try these 2 rules in any order

Rule 1
=AND(D41<>"",EXACT(D45,UPPER(D45)=FALSE)
Fill ---> Red

Rule 2
=EXACT(D45,UPPER(D45))
Fill ---> Yellow

If none of the two rules apply, nothing happens, ie, Fill -> none (default)

Hope this helps

M.

 

[RoryA]

Perhaps:
1) Cell D45 should be white if cell D41 is blank. Use =(ISBLANK(D41))
2) Cell D45 should be red if cell D41 is not blank and D45 is empty. Use =AND(D41<>"",D45="")
3) Cell D45 should be yellow if text in cell D45 is in upper case. Use =AND(D45<>"",EXACT(D45,UPPER(D45)))

 

[Marcelo Branco]

oops...

Rule 1
=AND(D41<>"",EXACT(D45,UPPER(D45)=FALSE)
Fill ---> Red

Rule 2
=AND(D45<>"",EXACT(D45,UPPER(D45)))
Fill ---> Yellow

M.

 

[weeksy]

Hi

Thanks for the quick reply. Unfortunately, this is causing the cell to be yellow whether D41 has an entry or not.

Hilary

I think you need just 2 rules

Try these 2 rules in any order

Rule 1
=AND(D41<>"",EXACT(D45,UPPER(D45)=FALSE)
Fill ---> Red

Rule 2
=EXACT(D45,UPPER(D45))
Fill ---> Yellow

If none of the two rules apply, nothing happens, ie, Fill -> none (default)

Hope this helps

M.

 

[weeksy]

FANTASTIC! Thanks Rory. This works perfectly.

So that I can apply this type of formula to other cells with slightly different conditions please can I ask you to annotate what the formula means? I@m not sure what the <> or "" represent.

Thanks for your speedy response.

Perhaps:
1) Cell D45 should be white if cell D41 is blank. Use =(ISBLANK(D41))
2) Cell D45 should be red if cell D41 is not blank and D45 is empty. Use =AND(D41<>"",D45="")
3) Cell D45 should be yellow if text in cell D45 is in upper case. Use =AND(D45<>"",EXACT(D45,UPPER(D45)))

 

[RoryA]

D41<>"" basically just tests that the length of D41 is not 0 (the <> means 'not equal to'). It will allow for empty cells as well as formulas that return "".

 

[weeksy]

Thanks. Really useful stuff. I find the Microsoft help pages so useless.

D41<>"" basically just tests that the length of D41 is not 0 (the <> means 'not equal to'). It will allow for empty cells as well as formulas that return "".