Conditional Formatting help needed please

Question

Hi, 

I need some assistance with conditional formatting please.  

I would like a row on a spreadsheet to change to Red if H2 says ‘Rejected’ but only if there are no blank cells in that row.  If there are blanks in that row I would like the cells in that row to change to yellow. 

I have worked out how to do the formulae to do either of these formatting requests but not the two combined together.

Also, is there a way to also have the H2 cell that says Rejected to be red regardless of whether there are blanks in the row or not with the rest if the row being red if no blanks, and yellow if there are blanks.

Thanks

J

Answer 1

Hi Jen

You need to set 3 CF rules to accomplish your goals

Rule 1  For the cells in column H that**say ‘Rejected’

1- Select the range of cells in column H

2- Then Format cells Equal to "Rejected"

3- Format RED

Rule 2Make entire row Red if the cell in column H says ‘Rejected’ but only if there are no blank cells in that row.

1- Select this time the entire data range

2- Go to CF \ New rule\ Use formula to determine...  =AND($H2="Rejected",COUNTA($A2:$N2)=14)

RESULTS so far

Rule 3 "... If there are blanks in that row I would like the cells in that row to change toyellow."

Similar procedure to Rule 2 but the formula to use this time is

=AND($H2="Rejected",COUNTA($A2:$N2)<14)

IMPORTANT

Make sure in the CF rules manager the rules were in the order as in the picture below

FINAL RESULT

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 2

Hi Jeovany,

Thank you for taking the time to help me with this I really appreciate it.

The above worked perfectly.  Its easy when you know how.

Thanks again.

Jen