Previous Topic Next Topic

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!


Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
wendy
Jun 23, 2011 at 04:02 PM
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 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

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Conditional formatting: change fontcolor based on another cell
wandanuila -
Ravi -

18 responses
Condition formatting on changing cell color based on another cel
Saitoh7 -
Mazzaropi -

1 response
Excel make cell flash red
hugrl -
Rjc -

13 responses
Code to move rows into another sheet based on certain values
nathaliecannell -
vcoolio -

1 response
Copy rows based on a condition
Blossom83 -
ravichennai86 -

30 responses