copying conditional formatting from one column to another with the criteria being a cell in the new column

Sid Taylor

New Member
Joined
Mar 4, 2011
Messages
8
Hello,
Appreciate the site and any help in advance. I have a spreadsheet with 49 columns and at the top of each column I have two rows. the first row is the average of the entire column and in the second row I will be selecting results from a store and want that cell to be red or green based on whether or not it is above or below the average listed in the cell above. I have created that rule in the conditional formatting with "using a formula to determine which cells to format" (=c2>c3 ) then C3 is filled with red and the converse is c3 is filled with green if greater than c2. but I have not been able to find a way to copy that formula to other columns. Excel seems to automatically add a $ to the cell location and any copy / paste or format painter use just ends up referring back to the original column. That means I would need to create 98 of these rules to cover each column.

Is there any way to copy that conditional formatting from one column to the next?
 

Attachments

  • Screenshot 2022-10-20 105406.png
    Screenshot 2022-10-20 105406.png
    5.6 KB · Views: 7

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi Sid,

As soon as you have selected a cell in the CF dialog hit F4 until all the $ is gone.

Cell Formulas
RangeFormula
C3:W3C3=RAND()*10
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C4:W4Expression=C4>C3textNO
C4:W4Expression=C3>C4textNO
 

Attachments

  • CF hit F4.png
    CF hit F4.png
    9.9 KB · Views: 10
Upvote 0
Thanks for the reply. Hitting F4 does change remove the $ signs but as soon as it's saved, they revert back to being an absolute reference and I'm not able to copy without the same issue
 

Attachments

  • Screenshot 1.png
    Screenshot 1.png
    26.2 KB · Views: 5
  • Screenshot 2.png
    Screenshot 2.png
    24 KB · Views: 5
Upvote 0
Hm, why not first delete all the conditional formatting rules from those cells and try again the above?
Maybe it gets confused.

1666739911562.png



Also, please note the following:

1666740127575.png


1666740052932.png
 

Attachments

  • 1666739964337.png
    1666739964337.png
    25.5 KB · Views: 3
  • 1666740035691.png
    1666740035691.png
    7.2 KB · Views: 3
Upvote 0
Solution
Thanks for this. My problem was not realizing I could enter the entire range in the applies to section with the rule section only having the single cell. Thanks again for the help and clarification!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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