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