CODEWITHSUNDEEP
vbaexcel
ERKSMTY
ERKSMTY

Reputation: 135

Conditional Formatting on Changing Range

I need to conditionally format three columns that move left/right depending on the data loaded. Condition is if cell value is blank (=""). I am really struggling to figure this one out.

Here's my setup:

Sub Conditional_Format()
    Dim ws2 As Worksheet
    Dim lRow3 As Long, lCol3 As Long
    Dim rng8 As Range, rng9 As Range

    Set ws2 = Worksheets("Unfav GT500 Comments")

    With ws2
        lRow3 = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol3 = .Cells(3, .Columns.Count).End(xlToLeft).Column

        Set rng8 = .Range(.Cells(4, lCol3 - 2), .Cells(lRow3 - 1, lCol3 - 1))
        Set rng9 = .Range(.Cells(4, lCol3), .Cells(lRow3 - 1, lCol3))
    End With
End Sub

Upvotes: 2

Views: 176

Answers (1)

Impuls3H
Impuls3H

Reputation: 303

For dynamically changing ranges, I suggest using a search to first location the columns. Is there any fixed unique column header that you're looking for and in any particular area of the spreadsheet? If so, use the below to search for it. You can change the format and the column header to suit your data.

'Below row will get you the column header. Repeat this line to search for the three columns.
ws2.Cells.Find(What:="Specific_Column_Header", LookIn:= xlValues).Activate
startrow = ActiveCell.Row

For row1 = startrow to ws2.Cells(.Rows.Count, "A").End(xlUp).Row
    If Cells(row1, ActiveCell.Column) = "" Then
        'Set your format here
        Cells(row1,ActiveCell.Column).Select
        With Selection.Interior
            .Pattern = xlSolid
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.15
        End With
    End If
Next

Upvotes: 1

Related Questions