Forum Discussion
Conditional Format Reference Ranges Shift Wildly
Very often when I create a new conditional format by formula, I will hit "ok" and close the window, expecting the rule to be applied, but it does not take effect in the ranges I specified. I now know...
If you want to create a conditional formatting rule for a range, select that range and make sure that the first cell in the range (i.e. the top left cell) is the active cell within the selection.
And make sure that the conditional formatting formula is correct with respect to the active cell.
For example, if you want to highlight a row in the range A2:K100 if the cell in column D contains Sunday, select A2:K100 and make sure that A2 (or at least a cell in row 2) is the active cell in the selection.
And use the formula =$D2="Sunday". It is essential that you use 2 as row number in the formula; this corresponds to the row number of the active cell.
Thanks a lot for your reply. So it sounds like I was kind of on the right track.. the issue is that I often don't have a cell in the to-be effective range selected when I create the rule, so when I change the effective range, it changes the reference I put in the rule formula.