Copy only conditional formatting

2
votes

I am struggling with something that seems fairly simple. I am trying to copy just the conditional formatting rules of a cell to another. I have searched for similar questions here:

1) Copy Format Conditions from one cell to another?

and elsewhere:

1) https://www.ozgrid.com/forum/forum/help-forums/excel-general/9956-solved-macro-to-copy-conditional-formatting

2) https://www.mrexcel.com/forum/excel-questions/369089-vba-copy-conditional-formatting.html

...but most answers tend to just say to use .PasteSpecial xlPasteFormats, which 9 times out of 10 does the job, but is not a true answer to what I am trying to do (see the comment from a user on the SO question: "This also copies more than just the conditional formatting but I can deal with it").

I am trying to take the conditional formatting rules from one cell and apply them to another cell that is formatted differently as standard. Judging by this set of examples of .PasteSpecial enums there isn't one that does the exact job - xlPasteAllMergingConditionalFormats looks like it comes close but I don't actually want to paste anything from one cell to another.

Tried a few hopeless guesses like:

For Each cond In rng1.FormatConditions rng2.FormatConditions.Add (cond) Next cond

But I know it needs more than that... Any help appreciated!

1
vbaexcelformattingconditional-formatting
The paste special dialog does have an option called "All Merging Conditional Formats", does that work for you? – jkpieterse
Yes, I mentioned this in my question but it isn't quite right.. so close.. but not just conditional formatting rules. See further detail in the comments of Vityata's answer below. Cheers – jackelsey

1 Answers

1
votes

This is a bit of a cheap trick (a.k.a. "The Swap"), but it should work. Let's say that you care only for the following properties:

  • Font.Name
  • Font.Size
  • Interior.Color

Thus, before copying the cell, you can simply remember the properties in a "hiddenRange". Thus, you can simply copy the copyRange to the hiddenRange, then copy the copyRange to the pasteRange with xlPasteAllMerginingConditionalFormats and then recall the correct properties of the pasteRange from the hiddenRange.

It is a bit better to be seen than explained:

Option Explicit

Sub TestMe()

    Dim hiddenRange As Range
    Dim copyRange   As Range
    Dim pasteRange  As Range

    Set hiddenRange = Range("A1")
    Set copyRange = Range("B2")
    Set pasteRange = Range("B3")

    pasteRange.Copy hiddenRange
    copyRange.Copy
    pasteRange.PasteSpecial Paste:=xlPasteAllMergingConditionalFormats

    With pasteRange
        .Font.Name = hiddenRange.Font.Name
        .Font.Size = hiddenRange.Font.Size
        .Interior.Color = hiddenRange.Interior.Color
    End With

    hiddenRange.Clear
    Application.CutCopyMode = False

End Sub