Report

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

Ask a question Tandar - Last answered on Mar 7, 2017 at 03:53 PM by Mario
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
+16
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
Was this answer helpful?  
zvproz Hassanov - Apr 6, 2016 at 07:47 AM
I can't tell more by english but I think can help you in that problem.
1. Change call all words "cell" -->cl
2. Change cell.EntireRow. -->cl.
That it all.
elDuderino- Apr 18, 2016 at 02:10 PM
How would you just highlight cells with a color just that have any information in them?
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

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!