Conditional Format Reference Ranges Shift Wildly

[tjryan35]

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 to immediately go back to the rule I wrote, where I will observe that the relative reference ranges I put in the formula are now shifted by many rows and columns. I can't find any literature online to explain why this happens. It's not a huge inconvenience, but I just wonder why it happens so consistently. 

Has anyone out there encountered the same thing? Do you know why it happens / how to prevent it?

UPDATE: I played around with it a bit, and it seems that it only shifts the references when I update the effective range. As in, at first I just had a random cell selected when I wrote the rule, then I pasted the effective range into that rule from another rule along the same lines. Only then does the reference range shoot out into oblivion and need to be fixed. Still not sure why...

Thanks!

[#2]

@tjryan35 

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.

[#3]

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.