Forum Discussion

Brass Contributor

Jan 18, 2023

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...

Jan 18, 2023

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 Sub

An alternative could be these lines of code. In the attached file you can run the macro for conditional formatting.

OliverScheurich

Gold Contributor

Jan 18, 2023

Vimal_Gaur

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 Sub

An alternative could be these lines of code. In the attached file you can run the macro for conditional formatting.

Vimal_Gaur
Brass Contributor
Jan 19, 2023
Awesome, this will do my job. I have 20 data entry feeder who will mark STATUS - Yes / No. By this I will quick identify all OK as well other things. The rows are not fixed, every data entry operator will get different number of rows. Now I want range should be the last row, can do it?
- OliverScheurich
  Gold Contributor
  Jan 19, 2023
  Vimal_Gaur
  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
  - Vimal_Gaur
    Brass Contributor
    Jan 21, 2023
    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