Excel 2019 Conditional formatting rules not applying consistently

0
votes

I'm applying the same basic conditional formatting rule to rows in excel but the rules are not working consistently. Specifically, I'm trying to change the color of the entire row based on the text contents of a cell in column Q. My rule is working for certain words/colors but not for others (please see image), even though the conditional formatting rule is the same.

enter image description here

The formulas for the rules are: 

=$Q3="Withdrew" (Fill cell with grey) (applies to: 'Sheetname'!$3:$499999)

=$Q3="Complete" (Fill cell with yellow, dark yellow text) (applies to: 'Sheetname'!$3:$499999)

=$Q3="Lost to follow-up" (Fill cell with light red) (applies to: 'Sheetname'!$3:$499999)

=$Q3="Randomized" (Fill cell with light green) (applies to: 'Sheetname'!$3:$499999)

=$Q3="Introductory module complete" (Fill cell with green) (applies to: 'Sheetname'!$3:$499999)

The last three rules are working fine (red and green fills) but the first two (grey and yellow) remain without a background fill even when the cells contain "Withdrew" and "Complete". I've checked spellings and that the cells addresses are correct.

Would appreciate any help! The attached image makes things a lot clearer, if you get a chance to look. Thank you!

1
excelconditional-formatting

1 Answers

1
votes

most likely, the values in the cells are not meeting the conditions. Leading or trailing blanks or other invisible characters can do that.

To troubleshoot, use a worksheet formula to see if =Q3="Complete" returns TRUE or FALSE and copy that formula down your rows. That way you can find erroneous entries.