Applying Conditional Formatting without overwriting existing formatting

TGreenwood

New Member
Joined
Nov 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Good morning

I am trying to set up conditional formatting on a spreadsheet. I've it set up correctly on the top row, and wish to apply it to the rest of the sheet.
However, I don't want to simply drag it down as it overwrites the existing formatting (not conditional).
Is there any way I can get the Conditional Formatting to apply to the rest of the sheet using a method other than the standard copy/paste/Format Painter etc?

Format Rule:
=$D13='Audit Exam task'!$H$3:$H$5

Applies to:
=$A$13:$S$13

I need it to cover every row on the sheet. I am aware that if the formula is true, it will overwrite the formatting, which I am fine with.

Thank you
 

Attachments

  • Audit Test Sheet.JPG
    Audit Test Sheet.JPG
    187.1 KB · Views: 5

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum. :)

Just edit the 13 at the end of the Applies To range to whatever row number you need. Note that unless H3:H5 is a merged cell, your current formula doesn't really make sense.
 

TGreenwood

New Member
Joined
Nov 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Welcome to the forum. :)

Just edit the 13 at the end of the Applies To range to whatever row number you need. Note that unless H3:H5 is a merged cell, your current formula doesn't really make sense.
Thanks Rory, but that hasn't worked?

And yes, H3:H5 is a merged cell.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What exactly does "hasn't worked" mean? I do this frequently, so I know it works. :)
 

TGreenwood

New Member
Joined
Nov 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What exactly does "hasn't worked" mean? I do this frequently, so I know it works. :)
I updated the Applied To range, and tested it, but the top line stayed green, not the one I was testing.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

No, since you used a relative row number in the $D13 part. (I'm assuming that you need to compare all rows to H3)

Did you change the value in D13 as well? If not, I'd expect it to remain green.
 

TGreenwood

New Member
Joined
Nov 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
No, since you used a relative row number in the $D13 part. (I'm assuming that you need to compare all rows to H3)

Did you change the value in D13 as well? If not, I'd expect it to remain green.
No, I changed the value in H3:H5, and want the corresponding row to highlight green, not D13.
e.g.: If the value in D34 is in H3:H5, then row 34 will highlight.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,819
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
So what do your Formula and Applies To boxes look like now?
 

TGreenwood

New Member
Joined
Nov 5, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Format Rule:
=$D13='Audit Exam task'!$H$3:$H$5

Applies to:
=$A$13:$S$34
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,404
Members
412,590
Latest member
Velly
Top