Copying cells with Conditional Formatting Rules

[Snake Eyes]

Greetings,
I have a range of cells that have working Conditional Formatting rules.
W374:X387

The formulas in the conditional format rules have absolute Column references and relative Row references.
An example of one: $W389


I want to copy these cells W374:X387 to W297:X310.

This second area W297:X310 is identical to the first and I want the contained formulas and conditional formats to work using the relative cell references for the 2nd area which would be $W312.

The formulas contained in the cells work, however, the Conditional Formats don't work until I make the cell references in the rules absolute for the Column AND the Row ($W$312).

The fix seems to be to use the absolute cell reference ($W$389) BEFORE I copy the groups of cells to the new area W297:X310.

When I do this and then copy the section over, the Conditional Format rule now references the original cell ($W$389) and not the relative cell it needs to ($W312).

How is one supposed to work around this?

BTW, once I figure this out, this copy process will need to work for 19 areas in the same worksheet so, I'm trying to avoid manually editing several conditional formatting rules 19 times.

 

[StephenCrump]

I'm not clear what you've got here ...

It sounds like for every cell in W297:X310, conditional formatting should refer to $W$312? This can be set up by selecting the whole range W297:X310, and applying one CF rule.

Which presumably means that similar should apply in W374:X387, with every cell referring to $W$389? If so, again, only one rule is needed.

But it sounds like you are using relative references $W389, and every line uses this relative reference $W389? This would imply you have set up at least 14 CF rules for this selection? But if this was the case, each relative reference should update correctly to $W312 when copied to the new range.

The gist of your post, I think, is that you have a block of conditional formatting all pointing to a particular row (absolute) adjacent to the block. You want to copy that block to another block and have that row reference adjust relative to the new block.

Depending on the number of copies, I'd consider using VBA to change the CF formulae. But if you're manually copying and pasting only 19 times, it wouldn't take much extra time to change the row number in the CF rule each time. But before doing this, I'd make sure there was only one CF rule in the original block to be copied, so that only 19 changes are required.

 

[Snake Eyes]

Maybe if I include a couple of screenshots this may clarify my concern.
I have a block of cells that have working Conditional Formatting rules.
W374:X387

The formulas in the conditional format rules have absolute Column references and relative Row references.
An example of one: $W389

I want to copy this block W374:X387 to W297:X310.
This second block W297:X310 is identical to the first and I want the contained formulas and conditional formats to work using the relative cell references for the 2nd block which would be $W312.

The formulas contained in the cells work, however, the Conditional Formats don't work until I make the cell references in the rules absolute for the Column AND the Row ($W$312).

The fix seems to be to use the absolute cell reference ($W$389) BEFORE I copy the block of cells to the new block W297:X310.

When I do this and then copy the block over, the Conditional Format rule now references the original cell ($W$389) and not the relative cell it needs to ($W312).

How is one supposed to work around this?

 

[StephenCrump]

If you have a CF formula in W374:X387 referring to $W389, and you copy W374:X387 to W297:X310 then the CF formula there will refer to $W312, based on the relative reference.

So if the CF in W374:X387 is working correctly, and you want it to work identically in W297:X310, the copy should work.

But I think you might be misunderstanding what you're starting with in W374:X387. The CF formula shown is the formula for the top left cell W374. The relative row reference means that:

W374:X374 refer to $W389
W375:X375 refer to $W390
W376:X376 refer to $W391
etc

Here's a simple example to illustrate:

	A	B	C
1
2		A	1
3		B	2
4		C	3
5		D	4
6		E	5
7
8			Hello
9
10			Hello
11			Hello
12
13
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=$C8="Hello"	text	NO

Whereas I think you wanted something more like this?

	A	B	C
1
2		A	1
3		B	2
4		C	3
5		D	4
6		E	5
7
8			Hello
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=$C$8="Hello"	text	NO

Here's an example of how you can use VBA to copy CF blocks, updating absolute references to make them relative to the new block(s).

Before:

	A	B	C
1
2		A	1
3		B	2
4		C	3
5		D	4
6		E	5
7
8			Yes
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=$C$8="No"	text	YES
B2:C6	Expression	=$C$8="Maybe"	text	YES
B2:C6	Expression	=$C$8="Yes"	text	YES

Sub CopyCF()

    Dim rng1 As Range, rng2 As Range, r As Range, i As Long, j As Long
    Dim step As Long
    Dim f As FormatCondition
    
    Set rng1 = Range("B2:C6")
    i = rng1.Rows.Count
    j = rng1.Columns.Count
    Set rng2 = Range("D13,C22")  'arbitrary copy destinations - first cell
    step = 2
    
    For Each r In rng2.Areas
        Set rng2 = r.Resize(rng1.Rows.Count, rng1.Columns.Count)
        rng1.Copy rng2
        For Each f In rng2.FormatConditions
            f.Modify xlExpression, , Replace(f.Formula1, "$C$8", rng2(i + step, j).Address)
        Next f
    Next r
    
    'hardcoded - to test this particular scenario
    Range("E19").Value = "Maybe"
    Range("D28").Value = "No"
        
End Sub

After:

	A	B	C	D	E	F
1
2		A	1
3		B	2
4		C	3
5		D	4
6		E	5
7
8			Yes
9
10
11
12
13				A	1
14				B	2
15				C	3
16				D	4
17				E	5
18
19					Maybe
20
21
22			A	1
23			B	2
24			C	3
25			D	4
26			E	5
27
28				No
29
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C6	Expression	=$C$8="No"	text	YES
B2:C6	Expression	=$C$8="Maybe"	text	YES
B2:C6	Expression	=$C$8="Yes"	text	YES
D13:E17	Expression	=$E$19="No"	text	YES
D13:E17	Expression	=$E$19="Maybe"	text	YES
D13:E17	Expression	=$E$19="Yes"	text	YES
C22:D26	Expression	=$D$28="No"	text	YES
C22:D26	Expression	=$D$28="Maybe"	text	YES
C22:D26	Expression	=$D$28="Yes"	text	YES

 

[Snake Eyes]

Thank you for the detailed explanation, examples, and VB Code StephenCrump.
I'll study this and work towards implementing the VB Code into my worksheet.

If I understand this, copying one block to another block and have the CF work correctly, absolute cell references but must be utilized ($W$389) along with the VB code to allow those absolute references to become relative to the new block once copied. Is this correct?

 

[StephenCrump]

If I understand this, copying one block to another block and have the CF work correctly, absolute cell references but must be utilized ($W$389) along with the VB code to allow those absolute references to become relative to the new block once copied. Is this correct?

I wouldn't say it this way. But I'm still not clear on what CF rules you have, and how they should be copied.

I am guessing that your setup is analogous to mine in Post #4, where you have a block of cells, with CF all referring to the same row. This is best set up as one CF rule, referring to an absolute row number.

The problem if you want to copy this CF to a new block is that you need to update that row reference. It could be done manually. But if there are too many to make this practical, VBA is one way you could automate the change. You could also look at having a helper column to reference in the CF formula to point you to the right row.

I am not aware of any smarter way that you can do this directly, rather than indirectly.