Excel VBA Conditional formatting

[Vimal_Gaur]

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

[#2]

@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

[#3 · Accepted solution]

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

[#4]

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?

[#5]

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?

[#6]

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.

[#7]

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.

[#8]

@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

[#9]

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