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

Tandar - Mar 23, 2011 at 06:34 PM - Latest reply: ac3mark 9968 Posts Monday June 3, 2013Registration dateModeratorStatus July 19, 2018 Last seen
- Nov 3, 2017 at 06:31 PM
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!


See more 

14 replies

Best answer
TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Mar 24, 2011 at 10:45 AM
17
Thank you
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

Thank you, TrowaD 17

Something to say? Add comment

CCM has helped 1677 users 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 2 Posts Friday November 3, 2017Registration date November 22, 2017 Last seen - Nov 3, 2017 at 06:21 PM
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 9968 Posts Monday June 3, 2013Registration dateModeratorStatus July 19, 2018 Last seen - Nov 3, 2017 at 06:31 PM
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!