Hi,
I'm having a problem with my conditional formatting rule applying to all cells in a range. I have a row of 20 cells, B3:U3 (rows 3 and 4 are actually merged together, not sure if this makes a difference), and in this row I will be entering "1", "2", and "S" one time each in any of the 20 cells. Once I enter one of each of those values, I'd like the rest of the cells in the range to turn grey to let me know that this row is complete (meaning within this range there is one "1", one "2", and one "S"). I used the following rule in the conditional formatting:
=AND(AND(COUNTIF(B3:U3,1)=1,COUNTIF(B3:U3,2)=1,COUNTIF(B3:U3,"S")=1),B3<>1,B3<>2,B3<>"S")
and tried to apply it across the range to the right, up to U3. However, if I enter a "1", "2", and "S" in any of the cells in the range, only the cells to the left of the first cell containing any of the values will turn grey. Is there a way to have all of the cells that are blank, both to the right and left and in between the three values, turn grey without changing the rule for every column?
I've attached a sample row from the workbook for reference. Thanks for your help!
Bookmarks