Excel VBA - Change cell color based on value

Solved/Closed
Tandar - Mar 23, 2011 at 06:34 PM
 Blocked Profile - 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!


1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Updated on Nov 30, 2018 at 03:48 PM
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
What if you want to change the color of another cell in another sheet. For example, in sheet1 I put yes or no in a cell range B2:B8, based on those values the cell range H2:H8 in sheet2 will turn green for yes and red for no. can you provide code for that?
Thanks
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 3, 2011 at 08:16 AM
you can change these lines
If cell.Value = "Yes" Then cell.Interior.ColorIndex = 10
If cell.Value = "No" Then cell.Interior.ColorIndex = 3


to some thing like this

   Select Case cell.Value
      Case Is = "Yes"
         'here column H is hard coded
         Sheets("Sheet2").Cells(cell.Row, "H").Interior.ColorIndex = 10
         
      Case Is = "No"
         'Here column H is calculated by adding 6 columns to column "B" (2) to get to column 8 (H)
         Sheets("Sheet2").Cells(cell.Row, cell.Column + 6).Interior.ColorIndex = 3
      Case Else
   End Select
0
What about non-exact text matches? for example, what if I want to find the value "Yes" but a cell in the range has the phrase "Yes, Please"...is there a way to get the formula to return colors for these non-absolutes?
0
Try adding a wildcard. Something like:

Case Is = "*Yes*"

Case Is = "*No*"

should get the job done.
0
Hey guys I am jumping on an old thread, however, I have nearly the same problem. but I have my code working pretty well but it highlights the Entirerow and I can't adjust it to set the range of the row to be highlighted. Please help:)

Here is the code I am using.
Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("f13: f5000 ")

For Each cell In MyPlage

Select Case cell.Value

Case Is = "Y"
cell.EntireRow.Interior.ColorIndex = 43

Case Is = "N"
cell.EntireRow.Interior.ColorIndex = 3

Case Is = "P"
cell.EntireRow.Interior.ColorIndex = 6



Case Else
cell.EntireRow.Interior.ColorIndex = xlNone


End Select
Next
End Sub
0