Conditional Formatting Relative Cell Changes when I click OK

[XLatExcel]

So I just had something pretty strange happen to me today. I was demonstrating adding a conditional formatting rule to the range D5:E10 and G5:H10 :

Formula
=D5>1.03 then format fill blue

I clicked on OK and Excel converted D5 into A5.

Of course when I went back to see why the rule wasn't working as advertised, I noticed that it had A5 so I changed it and all was well. Then I went to add the next rule over the same range noted above:

Formula
=And(D5<=1.03,D5>=0.97) then format fill green

and the same darn thing happened. Each instance of D5 turned into A5.

Does anyone know what is going on?

 

[tygrrboi]

Is D5 the first cell you click when you select the range? Where is the 'highlighted' cell of the selection?

For example, holding down control, I select D5-E10 and then G5:H10. For me G5 is the highlighted cell in the selection so I should reference my cells to G not D.

By referencing your cells to D with this selection, You are saying D in relation to G so, A in relation to D.... kind of .. Does that make sense? I do not know how to explain it technically, but hopefully you get what I'm saying.

 

[XLatExcel]

OH, I think I see what your saying. It has to do with Excel thinking the reference is offset 3 columns to the left and that is happening because I have two non-contiguous ranges selected. So then by this logic, Excel wouldn't be confused if I had selected D5:H10 and D5 in the formulas would have remained a reference to itself.

Thank you.

 

[tygrrboi]

I always try to pay attention to what cell is slightly different than the others when i select a non-contiguous range and base my offsets on that.