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

[dbish]

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?

 

[mart37]

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)

 

[dbish]

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