Ask a question Report

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

Tandar - Latest answer on Jul 3, 2011 08:16AM

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!

Answer

TrowaD 688Posts Sunday September 12, 2010Registration date ContributorStatus March 12, 2013Last seen Mar 24, 2011 10:45AM

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

Add comment

wendy - Jun 23, 2011 04:02PM

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

rizvisa1 4184Posts Thursday January 28, 2010Registration date ContributorStatus March 22, 2013Last seen - Jul 3, 2011 08:16AM

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

Reply to this topic

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

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

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!