Conditional Formatting - problem with absolute/relative reference ($) in formula

dbish

New Member
Joined
Feb 2, 2014
Messages
4
Hello All. I have a weird problem. Doing some simple conditional formatting. Want to fix one set of cells and then copy all formatting (font, size, conditional formatting)
using the format painter tool to many locations across different rows and columns. Screenshot show problem.
- Line 22: If I include $ for column reference in conditional formula (absolute reference) the formatting works as desired.
- Line 2: If I use only relative reference for columns in conditional formula then only first column is correct.

While this is a simple fix I have to copy formatting across many sets of columns and each one has be changed to the correct column reference in the conditional formula.

I am using Excel 2010 on a Windows 10 machine. Any thoughts?
 

Attachments

  • conditional format example.jpg
    conditional format example.jpg
    96.6 KB · Views: 18

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,058
Office Version
  1. 2016
Platform
  1. Windows
This isn't weird.
When you use CF in more than one cell, CF behaviour is like a normal formula with relative and absolute references.
When you have your lookup data in a column you have to use an absolute reference to that column ($E2)
 
Solution

dbish

New Member
Joined
Feb 2, 2014
Messages
4
So I changed the conditional formatting to only apply to one cell (Col B) usin relative formula. I then duplicated it to column C and D manually so each cell's conditional formatting only applied to that one cell. I could then copy the three cells to my next set of data and it worked!

Thanks - you can't believe how may ties and permutations i tried without figuring this out. Great Job! and duplicated it in each
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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
Top