CODEWITHSUNDEEP
vbaexcelconditional-formatting
shorton
shorton

Reputation: 353

Edit Existing Conditional Formats with Code

(Excel 2016) I have several large spreadsheets with existing conditional formatting. On any particular sheet I have several tables each with it's own set of conditional formatting. The tables change in size so the ranges vary. And there are lots of them (100's). The same rules apply to each set, that is, the conditional formatting I want to edit is consistent, with the same "set" of 4 formats that apply to each range/table (e.g. Red/Yellow/Green/Blue).

I'm trying to figure out how to edit a color value in the existing formatting. For example change a text color that is currently green, to light green. Right now I have to manually edit hundreds of formula entries.

I've seen lots of examples on applying conditional formatting via VBA, but I don't see how I can step through these tables and edit the existing formats, without changing their order or precedence.

Upvotes: 1

Views: 3105

Answers (1)

user4039065
user4039065

Reputation:

If all you want to do is modify the Fill color, cycle through all of the .FormatConditions on the worksheet and use a Select ... Case to modify the color ordinal.

Option Explicit

Sub wqwqty()
    Dim cfr As Long
    With Worksheets("Sheet1").Cells
        For cfr = 1 To .FormatConditions.Count
            With .FormatConditions(cfr).Interior
                Debug.Print .Color
                Select Case .Color
                    Case 255                'dark red
                        .Color = 192        'light red
                    Case 192                'dark red
                        .Color = 255        'light red
                    Case 5287936            'dark green
                        .Color = 5296274    'light green
                    Case 12611584           'dark blue
                        .Color = 15773696   'light blue
                    Case 49407              'orange
                        .Color = 65535      'yellow
                End Select
                Debug.Print .Color
            End With
        Next cfr
    End With
End Sub

Upvotes: 2

Related Questions