vba - 如何在 Workbook_BeforeSave 中的单元格中填充颜色

Question

我目前有以下代码:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Select

For i = 2 To 10
    If ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "YES" Then
        For j = 2 To 5
            If ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value = "" Then
                **<insert format here>**
            End If
        Next j
    End If


    If ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Value = "NO" Then
        For j = 2 To 5
            If ThisWorkbook.Sheets("Sheet1").Cells(i, j).Value = "" Then
                **<insert format here>**
            End If
        Next j
    End If
Next i

End Sub

现在。我试图在 If 语句中添加下面的代码，但没有奏效

ThisWorkbook.Sheets("Sheet1").Cells(i, j).Interior.ColorIndex = 3

我们如何为没有值的单元格添加填充颜色？

Answer 1

Interior.ColorIndex必须在范围内工作

也许是无法按预期工作的逻辑

尝试这个

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim i As Long

    With ThisWorkbook.Sheets("Sheet1")
        For i = 2 To 10
            Select Case .Cells(i, 1).value
                Case "YES"
                    If WorksheetFunction.CountA(.Cells(i, 2).Resize(, 4)) < 4 Then .Cells(i, 2).Resize(, 4).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 3
                Case "NO"
                    If WorksheetFunction.CountA(.Cells(i, 2).Resize(, 4)) < 4 Then .Cells(i, 2).Resize(, 4).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 2
            End Select
        Next i
    End With
End Sub