Excel VBA - Change cell color based on value

Tandar - Mar 23, 2011 at 06:34 PM
 Blocked Profile - Nov 3, 2017 at 06:31 PM

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)
        cell.Interior.Color = RGB(255, 0, 0)
    End If

End Sub

Any help is greatly appreciated!


1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 549
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
End Sub

Best regards,
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?
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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