Copy Conditional Formatting with Relative reference

emfjsullivan

New Member
Joined
Apr 25, 2022
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I am trying to copy Conditional Formatting rule from one cell to all the cells in the same column in Excel 2019

Here's my data:

A B
1 Col1 Col2
2 3 3
3 4 2
4 2 3
5 4 7

What I would like to do is have the fill turn red in Col2 when the value in Col2 > value in Col1. In the above, B4 and B5 should be red fill.

I was able to define conditional formatting for Col2 in the first row so that if Col2 becomes a 4, the fill becomes red (Col2 is actually a formula if that makes a difference).

What I would like to do is copy the conditional formatting rule of B2 to the other 3 rows (B3, B4, B5). I have a formula that works for B2 which is Cell Value > $A2. My understanding is if I have a $ in front of A, that value wont change but the 2 should become a 3/4/5 if I paste that formatting to the 3 other cells (B3, B4, B5). I've tried the following sequences with no luck, it always keeps $A2 and doesn't change to $A3, $A4, $A5 as expected.

I do a CTL-C to copy the B2 cell
I then drag my mouse to select B3..B5, then right click for Paste Special and have tried Keep Source Formatting and Merge Conditional Formatting but as I said, it always keeps the $A2 value as the comparison.

What am I don't wrong?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Welcome to the MrExcel board!

In my opinion, Copy/Paste Special is generally not the easiest/best way to apply conditional formatting to a range of cells.

Here are two way that you can consider after you have removed any existing CF in your range of interest (B2:B5)

1. Select B2:B5 (that is the whole range you want to apply the formatting to) then Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =B2>A2 -> Format... -> Fill tab -> Choose red -> OK -> OK

2. Again first remove any CF in the range. Now select just B2 then Conditional Formatting -> New rule ... -> Use a formula to determine which cells to format -> Format values where this formula is true:- =B2>A2 -> Format... -> Fill tab -> Choose red -> OK -> OK.
Now with B2 selected Conditional Formatting -> Manage Rules ..
You should see something like below and you can just change the 'Applies to:' box to read $B$2:$B$5 and click Apply -> OK

1650937337815.png
 
Upvote 0
Thank you very much for the quick reply and info. I used #1 and it worked very well.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)

Just to clarify, when you have a range selected, and you are entering a CF formula rule make sure that the references used in the CF formula relate to the active cell (usually the top-left cell of your selection)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top