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 all No then highlight entire row in RED
Condition3: Yes & No both then highlight entire row in YELLOW
Make function in Excel VBA
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.
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.
- 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?
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 The conditional formatting for RED/GREEN can be:
=PRODUCT(N($J3:$O3="yes"))and then highlight the whole table yellow so if RED/GREEN not true then Yellow is left. see attached
- Thanks for the solution, I learned a new thing. It is good but if rows increases the formula needs to be extended, I think it can only can be done manually and not automatically. That is why I want VAB sheet. However could please let me know what is 'N' stands for?
- So N() is a function that will return a number if excel recognizes it as a number, date or T/F and 0 otherwise. you could also use --( )
As for needing to 'extend' it if you make it a table as you insert/add rows the conditional formatting should expand also. even w/o it being formatted as a table if you insert rows the formatting rule should be expanded/added. but if you add rows after it might not catch it. The fix is simply expanding the 'applied to' range of the formatting, which isn't hard or could be done with VBA (lol). The formula itself won't change.
