Use VBA to Update Conditional Formatting Ranges?

Question

I'm trying to update the conditional formatting range for about 30 rows of data on a worksheet. Every month I update the data and I want to run a macro to adjust the ranges to incorporate the new month. I've already done this for my charts by updating the end of the series ranges by looping through the ChartObjects and SeriesCollection.

To do this on conditional formatting, everything I've found requires hardcoding a range (either a cell reference or a named range), e.g.:

With Worksheets(1).Range("e1:e10").FormatConditions(1) 

I'd prefer to just loop through the collection of conditional formatting for the worksheet, but I can't find any evidence of this collection existing in the Excel VBA Object Model. Am I missing something here?

Answer 1

This is a little convoluted since there isn't really any great way to loop through formatconditions in a sheet. But, you can loop through specialcells and then loop through their formatconditions and dig in from there:

Sub test()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim rngCell As Range
    Dim lng As Long

    For Each rngCell In ws.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
        For lng = 1 To rngCell.FormatConditions.Count
            On Error Resume Next
            Debug.Print rngCell.FormatConditions(lng).Formula1, rngCell.FormatConditions(lng).AppliesTo.Address
        Next lng
    Next rngCell
End Sub

I poached the specialcells() idea from Dick Kusleika's excellent post on this very subject at dailydoseofexcel.com blog.

Answer 2

You can use code as below: Here Based on condition Highlighted the row in yellow color. You can use your formatting

LastColumnARows = WB_Source.Sheets(SheetName.Name).range("A" & Rows.Count).End(xlUp).Row  

                    With WB_Source.Sheets(SheetName.Name)

                        For i = 2 To LastColumnARows
                            If .range("A" & i).Value > [Condition] Then
                                With .range("A" & i & ":E" & i)
                                    .Interior.Color = vbYellow
                                    .Font.Color = vbBlack
                                End With
                            End If
                        Next i
                    End With