Report

Copy conditional formatting for an entire row based on one cell [Solved]

Ask a question Maurits Kvåle 2Posts Wednesday October 2, 2013Registration date October 4, 2013 Last seen - Last answered on Nov 23, 2016 at 07:20 AM by j-bomb
Hello.

I'm working on an Excel sheet, where the entire row is coloured 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 colour (green), and if another cell is marked with an "x", the whole row gets one colour (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 automaticly 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 coloured based on the values in the first row.

And, I am going to apply this on 1000 rows, so it will take me atleast 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 appriciated. I've googled this for a while now and haven't found any solutions on the web yet.

Best regards
Maurits from Norway :-)
See more 
Helpful
+40
moins plus
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
PAULY- Jan 9, 2016 at 09:57 AM
GENIUS!! Thank you SOOOOOO much!!
Reply
Alice- Mar 31, 2016 at 10:49 AM
This just saved me so much time! Thank you!
Reply
Mark- Apr 26, 2016 at 07:52 AM
I've spent an hour trying to fix my spreadsheet, and now after reading this thread I've got it to work! Thank you!
Reply
grantsgirl- May 24, 2016 at 04:14 PM
Thank you I was working on a spreadsheet for an hour trying figure this out!
Reply
Zulma- Oct 29, 2016 at 05:11 PM
Thank You. I was about to give up!
Reply
Leave a comment
Helpful
+6
moins plus
Maurits, Good evening.

I feel glad to help you.
Have a nice weekend.

Greetings from Brazil
allan- Aug 10, 2016 at 11:58 PM
"you cannot use relative references in conditional formatting criteria for color scales, data bars, and icon sets."
Reply
allan- Aug 10, 2016 at 11:59 PM
i used "$B1" but didnt work :(
Reply
Leave a comment
Helpful
+1
moins plus
Yes, I figured it out.

With "$B1" for example.

Worked out great :-)

Thanks :-)
Leave a comment
Helpful
+1
moins plus
excellent, thanks
Leave a comment
Helpful
+1
moins plus
Greetings from Canada, $B1 instead $B$1 is great trick, it helped me too.
Thank you
Sanket- Jun 15, 2016 at 08:46 AM
It worked, thanks (Sanket from India)
Reply
Nauman- Nov 17, 2016 at 05:09 PM
THanks a lot. It HELPED. I was struggling for hours!!!
Reply
Leave a comment
Helpful
+0
moins plus
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
Kathi- Sep 19, 2016 at 08:46 AM
found it :-D
Reply
Tanya- Sep 19, 2016 at 12:44 PM
What was the solution to your query? I am facing the exact issue. Please help me.
Reply
Kathi- Sep 19, 2016 at 04:47 PM
Hi Tanya, you have to select the whole tablet row (not excel row, but tablet row), click Format painter and then you can just click on the first cell in the row you want to copy it into. Only remember that the conditional formatting has to be set as e.g. "$A9", not "$A$9" (relative reference).
Like this it was copied to the whole row :-)

Hope I wrote it fine, I am not really good in explaining excel :-D

Kathi
Reply
Leave a comment
Helpful
+0
moins plus
Thank you Very Much I was trying to do exactly the same thing as the person who was asking the question. I almost thought it was going to take couple of months to finish my spreadsheet.

Thanks
Leave a comment
Helpful
+0
moins plus
yay,,, thank you! Saved me more searching time! :)
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!