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.
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.- Dear, thanks for explaining the N(), I'll try it.
The data entry operators are not familiar with excel functions & tables.
I just give them data, neither I increase rows or the operators.
So a operator may get 100 rows or another one will get 150 or more.