Copy conditional formatting for an entire row based on one cellSolved/Closed

Question

Hello.

I'm working on an Excel sheet, where the entire row is colored based on the values of two cells in the current row. If one of the cells in the row is marked with an "x", the whole row gets one color (green), and if another cell is marked with an "x", the whole row gets one color (red).

This is OK, and I made that rule work perfectly. However, when I want to copy this to the next row, the formula wont change. It automatically comes with "$B$1" (for example), and I can't change it to be only "B1". So when I copy the conditional formatting from one row to the next, the second row will also be colored based on the values in the first row.

And, I am going to apply this on 1000 rows, so it will take me at least a year to change every formula individually.

Is there anything I can do to copy this conditional formatting to the next 999 rows, where it also switches the formula to the next cell underneath?

Any help would be really appreciated. I've googled this for a while now and haven't found any solutions on the web yet.

Best regards!

Mazzaropi · Accepted Answer

Maurits, Good morning.

Greetings from Brazil.

Try to use only $B1 instead $B$1

Tell us if it worked for you. 
Belo Horizonte, Brasil.
Marcílio Lobão

Maurits Kvåle · Answer

Yes, I figured it out.

With "$B1" for example.

Worked out great :-)

Thanks :-)

Kathi · Answer

Hi all,

this is an old question, but mine is related, so I don´t want to make a new one :-)

The copying is OK. Only with one negative:

In my table, I have CF that marks the whole row based on what is written in the 1. cell (a.k.a. if in A1 is "OK", the whole A row in a table will be marked green)
But if I copy this CF (via format painter) to B1, the CF for B1 applies only for B1, not for the whole B row

- the CF is as formula "=$A8="OK"", applies to "=$A$8:$V$8"
(I cannot change the applies to into "=$A8:$V8", as it always automatically changes to =$A$8:$V$8" :-(

- after copying the format (the CF) to cell A9, the formula changes to "=$A9="OK"", but the applies to changes only to "=$A$9"

Is there a way to copy the CF, so it also copies the "applies to" range (in this case that it will automatically set the applies to in A9 to ""=$A$9:$V$9"?

I have about 400 rows and absolutely no time to do it manually :-(

thanks a lot

Copy conditional formatting for an entire row based on one cell

3 responses

Similar discussions

Subscribe To Our Newsletter!