Hi ,
When you apply a Conditional Formatting rule to a range say J7 through J31 , Excel will always take J7 as the reference point.
Thus , if your formula refers to J9 , then Excel will take it that you always want to look at a cell two cells down from a cell , for the entire range ; thus to colour J7 , Excel will look at J9 , to colour J8 Excel will look at J10 ,...
Similarly , if you have selected the range J7:J31 , and your formula uses J4 , then Excel will take it that you always wish to look at a cell 3 cells above the cell , for the entire range ; thus to colour J7 , Excel will look at J4 , to colour J8 Excel will look at J5 ,...
Thus , if you really wish to look at the current cell , your CF formula has to refer to the first cell in the selected range ; if it is a multiple row , multiple column range , the formula has to refer to the top left hand corner cell.
In the present case , if the Applies To range is E1:E6 , the formula should use E1 , and not E3 , even though your data starts from E3.
Narayan