Forum Discussion
Solved
Excel VBA Conditional formatting
I have a sheet containing HEADINGS & STATUS The Headings contains particulars of a person The Status shows Yes or No Condition1: if all Yes then highlight entire row in GREEN Condition2: if al...
Sub rowcolor() Dim i, j, k As Long For i = 3 To 22 j = Application.WorksheetFunction.CountIf(Range(Cells(i, 10), Cells(i, 15)), "yes") If j = 6 Then Rows(i).Interior.ColorIndex = 4 Else k = Application.WorksheetFunction.CountIf(Range(Cells(i, 10), Cells(i, 15)), "no") If k = 6 Then Rows(i).Interior.ColorIndex = 3 Else Rows(i).Interior.ColorIndex = 6 End If End If Next i End SubAn alternative could be these lines of code. In the attached file you can run the macro for conditional formatting.
You can try this code. The number of rows is determined dynamically.
Sub rowcolor()
Dim i, j, k, l As Long
Rows("1:1048576").Interior.ColorIndex = 0
l = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To l
j = Application.WorksheetFunction.CountIf(Range(Cells(i, 10), Cells(i, 15)), "yes")
If j = 6 Then
Rows(i).Interior.ColorIndex = 4
Else
k = Application.WorksheetFunction.CountIf(Range(Cells(i, 10), Cells(i, 15)), "no")
If k = 6 Then
Rows(i).Interior.ColorIndex = 3
Else
Rows(i).Interior.ColorIndex = 6
End If
End If
Next i
End Sub
That was excellent, now is it possible to change it REAL TIME.
also
in addition to above conditions
by default all white
if any cell is blank out of 6 then orange
also
in addition to above conditions
by default all white
if any cell is blank out of 6 then orange