Extend range of existing conditional formatting to further cells?

[Requin]

Hi together,

I established one rule for conditional formatting, and it already applies to some cells.

I create now a macro which formats further cells; is there a way to apply exactly the same conditional formatting to these new cells? So that in the end, there is just ONE conditional formatting rule which applies to several cells, instead of several (same) conditional formatting rules, each of them applying to only one cell?

To clarify: I want to have just one cond. format. rule in my worksheet, which applies to several cells; my question is if the range of this rule can be extended by means of a macro.

Thanks,
Requin

[TMS]

I don't think you can edit the Conditional Formatting. I suspect that you have to delete what's there and re-apply it.

This is a recorded macro to set up CF in cell C7 to C19

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("C7:C19").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=C1="""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

And this is a macro which will loop through all the CF on a worksheet and create a list of the ranges and the formulas

Option Explicit

Sub lsform()
Dim cf As FormatCondition
Dim rng As Range

Dim vArray, i As Long

Set rng = Cells

If rng.FormatConditions.Count = 0 Then
    MsgBox "No CF"
    Exit Sub
End If

ReDim vArray(1 To rng.FormatConditions.Count, 1 To 2)
i = 0
For Each cf In rng.FormatConditions
    'Debug.Print cf.Formula1, cf.AppliesTo.Address
    i = i + 1
    'build array of conditions and addresses
    vArray(i, 1) = cf.Formula1
    vArray(i, 2) = cf.AppliesTo.Address
Next cf

' output array to same sheet, specified start cell
With Range("G1").Resize(UBound(vArray, 1), 2)
    .NumberFormat = "@"
    .Value = vArray
End With
' output array to another sheet, specified start cell
With Sheet2.Range("A1").Resize(UBound(vArray, 1), 2)
    .NumberFormat = "@"
    .Value = vArray
End With

End Sub

You can also check the interior color and the font color, etc:

?cf.Interior.Color
 65535 
?cf.Font.Color
Null

So, if you first establish the range, the formula, the background, font, borders, whatever, you should be able to adjust the range and re-apply the CF.

[Requin]

Thank you for your answer! Sounds compliated, but I will give it a try...

[TMS]

You're welcome. Thanks for the rep.