Report

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

Ask a question Tandar - Last answered on Nov 3, 2017 at 06:31 PM by ac3mark
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!


Helpful
+18
plus moins
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
Dovile- Aug 3, 2016 at 08:58 AM
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?
MH- Jan 25, 2017 at 01:45 PM
Just delete "EntireRow"
cell.Interior.ColorIndex=10
Reply
Mario- Mar 7, 2017 at 03:53 PM
Thank you for the script!
Reply
Qwentar 1Posts Friday November 3, 2017Registration date November 3, 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
Reply
ac3mark 8203Posts Monday June 3, 2013Registration date ModeratorStatus November 10, 2017 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!
Reply

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!