CODEWITHSUNDEEP
excelvba
twgardner2
twgardner2

Reputation: 660

Use VBA to Update Conditional Formatting Ranges?

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?

Upvotes: 1

Views: 7439

Answers (4)

Major_Havoc
Major_Havoc

Reputation: 1

New to posting. I ran into this problem tyring to cycle through all Format Conditions in order to expand them to the new data set. While using a For Each Next loop, it bombed every single time and just shut down Excel. What I stumbled onto after trying several different things was that after I counted the format conditions, I could then cycle through those conditions by number and that worked without issue.

Dim AllFormatConditions As FormatConditions
Dim intLastRow As Integer
Dim intFCCount As Integer
Dim intFCRow As Integer
Dim intFCNum As Integer
Dim strRange As String

Set AllFormatConditions = ThisWorkbook.Sheets("Exec Summary").Cells.FormatConditions 'Format Conditions Collection
intLastRow = Range("ExecSumStart").Offset(2000, intHomesLoc - intAnchorLoc).End(xlUp).Row 'Last Row of data for FCs to expand to
intFCCount = AllFormatConditions.Count 'Count of Format Conditions for the Range
intFCRow = Range(Range("execsumstart").Offset(1, 0).FormatConditions(1).AppliesTo.Address).Rows.Count + 3 'Last Row of Current FC Applies-to Range

For intFCNum = 1 To intFCCount
    strRange = Replace(AllFormatConditions(intFCNum).AppliesTo.Address, intFCRow, Trim(Str(intLastRow)))
    AllFormatConditions(intFCNum).ModifyAppliesToRange Range(strRange)
Next

Upvotes: 0

DecimalTurn
DecimalTurn

Reputation: 4129

To loop over the existing Format Conditions in a spreadsheet, you can use a loop like this:

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MySheet")

    Dim AllFormatConditions As FormatConditions
    Set AllFormatConditions = ws.Cells.FormatConditions

    Dim fc As Object    
    For Each fc In AllFormatConditions
        Debug.Print fc.AppliesTo.Address
    Next

Now, if you want to modify the AppliesTo range instead of just printing the address like above, you'll need to use ModifyAppliesToRange and pass a range as your argument.

eg.

fc.ModifyAppliesToRange ws.Range("A42:Z42")

Upvotes: 1

Vijay Dodamani
Vijay Dodamani

Reputation: 374

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

Upvotes: 0

JNevill
JNevill

Reputation: 50034

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.

Upvotes: 1

Related Questions