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 552
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
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
0
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
1

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