I could not copy same conditional formatting to other cells

Question

For example I created conditional formatting using formula with red, but after I finishing this I copy all the way to the right, selecting cells to apply to ; It worked when I dragged them to the right But it does not work when I drag down.

Here is my formula

Here is my table

So I did this manually I also tried CONTROL+C and PASTE formats The problem is I changed references on top I do not know starting reference what should be and every time I wanna apply to both rows and columns what formula should I write?

Answer 1

If you have equal spaces between low and high blocks of data, you can use offset inside condition

1. Select columns E:J
2. New Rule > Use formula
3. Type formula: =E1>OFFSET(E1,-7,0) (7 is a distance between each lower and higher cell to check
4. Set desired color and press ok

Since formula relative so will update for each cell