Copying Conditional formatting with relative cell referencces in the formula desn't work

[Roger Dance]

I have a cell H19 conditionally formatted "Format values where this formula is true" with: ="h19>$C$19" and then the colour green

When I copying the cell with Format Painter to cells H20 to H90, the formula doesn't change the relative reference to h20, h21.. h90 etc.. It stays as h19.

What am I doing wrong?

[#2]

Thanks very much!!!!

[#3]

Okay, I'm trying these methods, and it's not working for me.  In my case, I have 2 columns with values, A and B.  Column A contains fixed values.  Column B has values either entered manually, or from a formula.

Example:

1413	1794
82	82
37	37
324	458

For cell B1, I have the following two conditional formats applied:

Rule (applied in order shown)	Format	Applies to
Cell Value = $A$1	AaBbCcYyZz	=$B$1
Cell Value <> $A$1	AaBbCcYyZz	=$B$1

So I'm chaging the text color of B1 to green when the value matches A1, or red if it doesn't match.

I need to copy both formatting rules to the rest of column B, with each cell referencing it's A column counterpart.  Example of how it should look:

1413	1794
82	82
37	37
324	458

None of the formatting copy methods I've tried are working.  Each one is retaining the original A1 cell value comparison.

Am I missing something, or have I overlooked a step?

[#4]

@krozar 

It shall be relative reference for the row, e.g. Cell Value = $A1. Otherwise you always compare with the value in A1.

[#5]

@Sergei Baklan 

Thank you for calling out the one subtle difference that allows the format painter to retain relative references!

For those still confused, notice the differences to the rule cell values as shown below:

Original Rules

Rule (applied in order shown)	Format	Applies to
Cell Value = $A$1	AaBbCcYyZz	=$B$1
Cell Value <> $A$1	AaBbCcYyZz	=$B$1

New Rules

Rule (applied in order shown)	Format	Applies to
Cell Value = $A1	AaBbCcYyZz	=$B$1
Cell Value <> $A1	AaBbCcYyZz	=$B$1

Changing $A$1 to $A1 allowed the format painter to paste relative values to rest of the cells.

Thanks for you your help @Sergei Baklan 

[#6]

@100WattWalrus 

Hi Rob, how did you manage to copy the formula and apply it to the other rows without the formula using the original cell.value as the reference?

[#7]

@carla_db 

Sorry, but that was so long ago, I have no idea. It just had something to do with making sure my references were relative.

[#8]

Hi. How did u make it work on your end? I still cant seem to figure it out on my end :(

[#9]

@Sergei Baklan 

I've come back to this mid-year to update the spreadsheet some for my wife.

I am new to conditional formatting but have tried to follow the example - even typing your example letter by letter in the CF Rule Manager.

It' not working for me.

Rule

Formula ="d13>BUDGET!D13"

Format

RED text, red Fill

Applies to:

Summary!$D$13:$D$52,Summary!$E$13:$E$52,Summary!$F$13:$F$52,Summary!$G$13:$G$52,Summary!$H$13:$H$52,Summary!$I$13:$I$52,Summary!$J$13:$J$52,Summary!$K$13:$K$52,Summary!$L$13:$L$52,Summary!$M$13:$M$52,Summary!$N$13:$N$52,Summary!$O$13:$O$52

Nothing happens on the Summary Sheet cells that I am trying to CF.

Perhaps you see something obvious [to you] that might assist me.

[#10]

@varocketry 

Perhaps you mean

=d13>BUDGET!D13

not

="d13>BUDGET!D13"

[#11]

@Sergei Baklan 

I'll try that.  I am completely unaware the " " is required.  Nor do I understand their operation.

But, I'll try it.  Thanks again.

[#12]

@varocketry 

Perhaps you may submit sample file removing all extra and sensitive information

[#13]

@Roger Dance 

I just figured this out. If you copy the cell with conditional formatting and paste it TO ONE OTHER CELL it retains the conditional formatting and changes it relative to the cell.

If you copy and paste it TO A RANGE of cells, it does NOT change the cell relative to the others.

This means you have to ctrl+C/V a bunch of times to do a range, but it takes less time than going through the conditional formatting section and updating and achieves what you want.

[#14]

@Roger Dance I spent hours trying to figure this out. I finally came across a video that actually gave me the information I was looking for:

https://www.youtube.com/watch?v=uQmKnkPQ-pA

[#15]

Bookmarked! Thank you!

[#16]

@CStendardo    I watched that video, thanks.  And the one after it on 8 COnditional Formatting Tips  and was able to work out the issue .  Success.

Best tip from video was to craft the conditional test in an adjacent cell to determine if it evaluates TRUE or not.   Then copy that working formula to the window inside Conditional Formatting to enter the formula correctly.

I was able to select a column of number and then enter the conditional formatting function successfully.   I then selected the working formatted column's rows and used the PAINTER icon tool to copy that formatting to other columns.

[#17]

@krozar 

BUDGET - C	ACTUAL - D
454	454
258	258
159	155
357	357
654	654

Two Rules:  formulas

=D11:D15<>C11:C15     Red

=D11:D15=C11:C15     Green

Applies to: =D11:D15<>C11:C15     same in both rules

[#18]

@AaronWalker 

In general

=D11<>C11     Red
=D11=C11     Green

will be more correct

[#19]

@Sergei Baklan 

What you are showing in your reply will not work.  However, the format I presented works for me in my workbook which was attached.  Since I am new to this, I am not sure you see it.  Anyway, good luck.

[#20]

@AaronWalker 

Please check in Sheet2 attached

[#21]

@Sergei Baklan 

Yes, looks like both methods will work provided that the first row of data is selected in the current section.  All good!  Thanks for sharing.