copy conditional formatting between hyperlinked cells

Question

How do I ensure the conditional formatting of a cell is also applied to its hyperlinked cell as well?

following is a basic example of what I am trying to do. Cell E6 has conditional formatting. Cell M7 is hyperlinked to cell E6. How do I ensure that M7 has the same formatting as E6?

Answer 1

Use Hyperlink.SubAddess to get a reference to its target range. Next copy the target range and use Hyperlink.PasteSpecial xlPasteFormats to copy all the formating over. If you just want the Conditional Formatting then you'll have to itererate over the target's FormatConditions.

Sub ProcessHyperlinks()
    Dim h As Hyperlink
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        For Each h In ws.Hyperlinks
            If h.SubAddress <> "" Then
                On Error Resume Next
                h.Range.FormatConditions.Delete
                Range(h.SubAddress).Copy
                h.Range.PasteSpecial xlPasteFormats
                On Error GoTo 0
            End If
        Next
    Next

End Sub

Answer 2

tl;dr Extend the Applies to range with ,M7

Include M7 in the Applies to range for the CF that already is formatted conditionally. For example assuming conditional formatting of E6:G24 is conditional upon the value of F15 being AAAA, such as with this formula rule:

  =$F$15="AAAA"

with applies to of $E$6:$G$24.

In Conditional Formatting Rules Manager select Applies to and append M7:

It is however possible that the functionality will depend on your exact situation.