Need help with cell highlight

[noobsaibotcore]

Hi all, need help with cell conditional highlight.

- If CELL B4 to B8 have the text "Y" or "N/A", CELL A1 to be highlighted in green.
- If any from CELL B4 to B8 is not "Y" or "N/A", CELL A1 to be highlighted in yellow.
- If any from CELL B4 to B8 is blank, CELL A1 have no highlight.

 

[jasonb75]

Try setting the rules as below, make sure that the rule for the green highlight it at the top.

Book1
	A
1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF(B4:B8,"Y")+COUNTIF(B4:B8,"N/A")=5	text	NO
A1	Expression	=COUNTA(B4:B8)=5	text	NO

 

[noobsaibotcore]

Thanks, it works.

Possible to get,
- If any from CELL B4 to B8 is blank, CELL A1 have no highlight.

 

[jasonb75]

Do you have formulas in B4:B8?

If the cells are empty (no formulas) then a blank cell would cause A1 to revert to default state (no highlight). A formula would mean that it is counted, so you would need to change the formula for the yellow rule to

=COUNTIF(B4:B8,"?*")=5

 

[noobsaibotcore]

No formulas in B4:B8. Just need like below.

1:

2:

3:

 

[jasonb75]

If the cells are empty then there will be no highlight. You don't need to use a rule for that.

 

[noobsaibotcore]

1. - If CELL B4 to B8 have the text "Y" or "N/A", CELL A1 to be highlighted in green. < This works.

2. - If any from CELL B4 to B8 is not "Y" or "N/A", CELL A1 to be highlighted in yellow. < Does not work.

3. - If any from CELL B4 to B8 is blank, CELL A1 have no highlight. < This works.

 

[jasonb75]

If you have done as instructed in post 2 then it works correctly. I can't see from your screen captures what the problem is because they only show the final sheet, not how you have set the rules up.

Edit:- I've just noticed the difference. In the second image, B8 is blank so there is no highlight.

You asked for no highlight if 'Any' cell is blank, which is what I provided. Your images suggest that it should be no highlight if 'All' cells are blank, not 'Any'. Please clarify which is actually required, the answers we give you are only as good as the questions you ask.

You could try changing the yellow rule to

=COUNTA(B4:B8)

(removing the =5 from the end). This may or may not give you what you need depending on the accuracy of the other information that you have provided.

 

[noobsaibotcore]

=COUNTA(B4:B8)

Above rule works!! Thanks man!