Excel conditional formatting not working? Check the range and formula

In Manage Rules, show rules for the worksheet if the rule is missing from the current selection. Look at Applies to and check that it covers the first and last cells you expect. If only newly added rows are missing the color, check whether they fall outside the range. Pasting formatted cells can also introduce another rule, so look for rules that overlap.

Suppose Applies to is =$A$2:$D$20 and you want to color a whole row when column D is greater than 100. Use =$D2>100. The dollar sign before D keeps the check on that column, and the row number changes as Excel evaluates the rows. If you use =$D$2>100, every row checks the same cell. Try 101 in D2 and 99 in D3 to check that only the first row is highlighted.

A formula rule needs a true or false result. Test the same condition in a spare cell to see whether it returns an error. If an error should count as a non-match, handle it in the rule. When two rules try to set different fills on the same cells, the rule higher in the list takes priority. If a higher rule has Stop If True checked and its condition is met, Excel skips the rules below it for those cells.