Conditional Formatting keeps putting absolute references - ARGH help

[TomStarz]

Hello, I hope you are having a good day.

I'm trying to apply a simple conditional formatting using a formula condition:
AH8<>H8 (if does not equal) fill with yellow background for AH8 if this is the case
and AH8 has a formula in it =H8.
So basically if value is different in AH8 than what is in H8 for example 80 instead 35, I want that 80 to be highlighted in yellow.

The wizzard for conditional formatting keeps putting $ signs to make them absolute, so cannot drag the formula to AI, AJ, AK etc
so for AI it should check for AI8<>I8 but you still have formula AH8<>H8 and than applies to $AH$8:$AI$8

I have to be honest, this is driving me crazy haha, oh dear. Help - and I though I'm proficient in excel

 

[Joe4]

Welcome to the Board!

I never have had good luck with the Format Painter. I find it is much better to select the whole range you want to apply it to first.

Select the whole range that you want to apply it to (i.e. AH8:AK8) and then write the formula for the FIRST cell in that range, i.e. write your Conditional Formatting formula like this:

=AH8<>H8

Excel will automatically adjust it for all the other columns, and because you have not put "$" in there, the columns will "float" automatically as you move across.

Note that if you look at the rule afterwards, it may look a little funny (not what you expect), but should behave the way you want it to.

 

[DanteAmor]

Hi @TomStarz
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

More information with an image:

 

[Joe4]

Hi @TomStarz
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

More information with an image:

View attachment 93610

Nice explanation, Dante!

 

[TomStarz]

Welcome to the Board!

I never have had good luck with the Format Painter. I find it is much better to select the whole range you want to apply it to first.

Select the whole range that you want to apply it to (i.e. AH8:AK8) and then write the formula for the FIRST cell in that range, i.e. write your Conditional Formatting formula like this:

=AH8<>H8

Excel will automatically adjust it for all the other columns, and because you have not put "$" in there, the columns will "float" automatically as you move across.

Note that if you look at the rule afterwards, it may look a little funny (not what you expect), but should behave the way you want it to.

Thank you!
"Excel will automatically adjust it for all the other columns, and because you have not put "$" in there, the columns will "float" automatically as you move across."
Ha!, didn't assume it will do it automatically. Though I will have to copy is across manually.
Thank you very much for this - this has solved the problem. Many thanks again.

Best wishes,
Tom

 

[TomStarz]

Hi @TomStarz
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

More information with an image:

View attachment 93610

OMG! thank you for warm welcome and for your effort.
didn't know that "Excel will automatically adjust it for all the other columns, and because you have not put "$" in there, the columns will "float" automatically as you move across."

All good now., thank you again.
Best wishes,
Tom

 

[Joe4]

You are welcome.
Glad we were able to help!