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!

Excel vba color cell based on value
Vba color cell based on value - Best answers
Cell color excel vba - Best answers
Number to words in excel without vba - Guide
Based on the values in cells b77:b81, what function can automatically return the value in cell c77? ✓ - Excel Forum
Enable vba in excel - Guide
Vba case like - Guide
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum

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

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?

Conditional formatting: change fontcolor based on another cell

Condition formatting on changing cell color based on another cel

Excel make cell flash red

Code to move rows into another sheet based on certain values

Copy rows based on a condition

Similar discussions

Subscribe To Our Newsletter!