lundi 6 novembre 2017

VBA excel how to fix the code (exclude rows that are empty ) do not fill the background in a color

i have a code written in VBA that will check the date and base on the it will fill the background with the appropriate color.

i have cells (A to G ).

i want to check if the column C is empty i want to keep the row transparent

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

the problem is that the code step into the if statment to check if empty ... then it step into the last if statement and fill all the empty rows with the specified color.

CODE:

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 "F" 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

Aucun commentaire:

Enregistrer un commentaire