I could not copy same conditional formatting to other cells

user_17797021 0
Viewed 41

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?

excel
2 Answers

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

user_180845270

Your conditional formula has absolute references to the row: E$13 > E$6

But the "Applies to" describes cells all in a single row. So locking the formula to refer to a specific row makes no difference. (If anyone can word a clearer explanation please submit an edit!)

If you change the formulas to E13 > E6 you should be able to copy the formatting across and down your sheet.

Make the formula correct for the first cell in "Applies to", and Excel will apply it to the rest of the range as if you'd copied and pasted it.

So you could probably set a single rule: E11 > E4, and apply it to all the blocks: E11:F13;I11:J13;E17:F19;I17:J19;...

user_24498570
Related