VBA excel如何修复代码(排除空行)不要用颜色填充背景

Question

>我有一个用VBA编写的代码，它将检查日期并在此基础上用适当的颜色填充背景。

我有细胞（A 到 G）。

我想检查 C 列是否为空，我想保持行透明

  If IsEmpty(Cells(i, 3)) Then
         Range("A" & i & ":G" & i).Interior.Color = xlNone

问题是代码单步进入 if 语句以检查是否为空......然后它单步进入最后一个 if 语句，并用指定的颜色填充所有空行。

法典：

Private Sub CommandButton1_Click()
Dim i As Long
For i = Range("C5000").End(xlUp).Row To 2 Step -1 'Range upto 5000, chnge this as per your requirment'
    If IsEmpty(Cells(i, 3)) Then
         Range("A" & i & ":G" & i).Interior.Color = xlNone
       ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) < 0 Then
             Cells(i, 3).Interior.Color = vbGreen
       ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) = 0 Then
             Cells(i, 3).Interior.Color = vbYellow
       ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 1 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 4 Then
              Cells(i, 3).Interior.Color = vbRed
       ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 5 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 10 Then
               Cells(i, 3).Interior.Color = vbCyan
    Else
                Cells(i, 3).Interior.ColorIndex = xlNone
 End If
    ' your 2nd criteria to color the entire row if "G" is not empty
    If Trim(Range("G" & i).Value) <> "" Then
                       Range("A" & i & ":G" & i).Interior.ColorIndex = 15

  ElseIf Trim(Range("G" & i).Value) = "" Then
                       Range("A" & i & ":B" & i).Interior.Color = RGB(255, 189, 189)
                       Range("D" & i & ":G" & i).Interior.Color = RGB(255, 189, 189)
 End If
Next
End Sub

Answer 1

试试这个，只是在你的第二个语句中添加了一个额外的 If 子句

Private Sub CommandButton1_Click()
Dim i As Long
For i = Range("C5000").End(xlUp).Row To 2 Step -1 'Range upto 5000, chnge this as per your requirment'
    If IsEmpty(Cells(i, 3)) Then
        Range("A" & i & ":G" & i).Interior.Color = xlNone
    ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) < 0 Then
        Cells(i, 3).Interior.Color = vbGreen
    ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) = 0 Then
        Cells(i, 3).Interior.Color = vbYellow
    ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 1 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 4 Then
        Cells(i, 3).Interior.Color = vbRed
    ElseIf (VBA.CDate(Cells(i, 3)) - VBA.Date()) >= 5 And (VBA.CDate(Cells(i, 3)) - VBA.Date()) <= 10 Then
        Cells(i, 3).Interior.Color = vbCyan
    Else
        Cells(i, 3).Interior.ColorIndex = xlNone
    End If
    ' your 2nd criteria to color the entire row if "G" is not empty
    If Not IsEmpty(Cells(i, 3)) Then
        If Trim(Range("G" & i).Value) <> "" Then
            Range("A" & i & ":G" & i).Interior.ColorIndex = 15
        ElseIf Trim(Range("G" & i).Value) = "" Then
            Range("A" & i & ":B" & i).Interior.Color = RGB(255, 189, 189)
            Range("D" & i & ":G" & i).Interior.Color = RGB(255, 189, 189)
        End If
    End If
Next i
End Sub