vba - 更改范围的条件格式

Question

我需要根据加载的数据有条件地格式化向左/向右移动的三列。条件是单元格值是否为空白 (="") .我真的很难弄清楚这一点。

这是我的设置:

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

Answer 1

对于动态变化的范围，我建议使用搜索来首先定位列。在电子表格的任何特定区域中是否有您正在寻找的固定唯一列标题？如果是这样，请使用以下内容进行搜索。您可以更改格式和列标题以适合您的数据。

'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