Excel VBA - Change cell color based on value [Solved/Closed]

Report
-
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
-
Hello,

I've searched for examples and tried many different things, but it's just not working. This is a personal project I'm working on and would love some help to get it solved and running.

I've made a list and I want to go through a column (with an UNKNOWN set Range, items may be added or removed), and if the cell value says "Yes" I want it colored Green. If it says "No" I want it colored Red.

Here is what I have so far...


Option Explicit

Sub ChangeColor()

Dim cell As Range
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

    If Range("C2").End(xlDown).Offset(2, 0).Value = "Yes" Then
    
        cell.Interior.Color = RGB(0, 255, 0)
        
    Else
    
        cell.Interior.Color = RGB(255, 0, 0)
        
    End If
    
Next


End Sub


Any help is greatly appreciated!


1 reply

Posts
2591
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
February 11, 2020
396
Hi Tandar,

I'm a little confused comparing your story with your code.
Try this code and see if it yields the desired result:

Sub ChangeColor()
lRow = Range("C" & Rows.Count).End(xlUp).Row
Set MR = Range("C2:C" & lRow)
For Each cell In MR
If cell.Value = "Yes" Then cell.Interior.ColorIndex = 10
If cell.Value = "No" Then cell.Interior.ColorIndex = 3
    Next
End Sub

Best regards,
Trowa
19
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 4139 users have said thank you to us this month

hello, i tried this:
Sub colortest()
Set MyPlage = Range("y6:y313")
For Each cell In MyPlage
Select Case cell.Value
Case Is = "Pilot*"
cell.EntireRow.Interior.ColorIndex = 0
Case Is = "Flying*"
cell.EntireRow.Interior.ColorIndex = 0
Case Else
cell.EntireRow.Interior.ColorIndex = 10
End Select
Next
End Sub

I need that all cells that contains Pilot or Flying in it woudl be white, and all other green. But it always marks everything green... I tried to put * of all sides but it still the same. Could some one help me please?
Just delete "EntireRow"
cell.Interior.ColorIndex=10
Thank you for the script!
Qwentar
Posts
2
Registration date
Friday November 3, 2017
Status
Member
Last seen
November 22, 2017
1
I made a few modifications to your code. Setting the colour in just one spot is a lot easier to troubleshoot compared to in multiple Then statements: you're minimizing the code you rewrite (hence chances for bugs to occur), and using select / case / else allows one to copy & paste into other projects with easy to locate aspects to change. You could add other cases, have a default colour (in this case #ff0000), etc.

Sub ChangeColor()
lRow = Range("C" & Rows.Count).End(xlUp).Row
Set MR = Range("C2:C" & lRow)
For Each cell In MR
Select Case cell.Value
Case "Yes"
cell_colour = 4
Case "y"
cell_colour = 4
Case Else
cell_colour = 3
End Select
cell.Interior.ColorIndex = cell_colour
Next
End Sub
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
And there you go TrowaD, he improved on YOUR code!! How thoughtful!

I guess he is assuming the OP has the knowledge of Case and such, considering they are asking about colors in VBA! I guess we should have assumed the OP had advanced programming logic!

Thanks for making that a much simpler solution Qwentar!