Excel VBA - Change cell color based on value

user_14866867 0
Viewed 336

In the table in the indicated range, I would like to mark the cell with the value in white, and the other cells with no value in gray. I have code but it doesn't produce any result. There is no error either. What to change to make it work

For Each cell In wbMe.Sheets("page3").Range("B76:K89")
If cell.Value = "Yes" Then cell.Interior.ColorIndex = 10
If cell.Value = "No" Then cell.Interior.ColorIndex = 3
   Next cell

enter image description here

vba excel
2 Answers

Please, run the next code. It will automatically place conditional formatting in the range you need:

Sub makeCondFormatting()
  Dim sh As Worksheet, rng As Range, cond1 As FormatCondition, cond2 As FormatCondition
  Set sh = ActiveSheet
  Set rng = sh.Range("B76:K89")
  With rng
     .FormatConditions.Delete
     Set cond1 = .FormatConditions.Add(xlExpression, Formula1:="=" & rng.cells(1, 1).Address(0, 0) & " <> """"")
     Set cond2 = .FormatConditions.Add(xlExpression, Formula1:="=" & rng.cells(1, 1).Address(0, 0) & "  = """"")
  End With
  With cond1
        .Interior.color = RGB(255, 255, 255)
  End With
  With cond2
        .Interior.color = RGB(197, 198, 198)
  End With
End Sub

It will make the range cells change their interior color automatically when the cell is empty, or not.

user_22333080

try this code

Sub SetColor()
    Dim r As Range
    Set r = ThisWorkbook.ActiveSheet.Range("B2:B7")
    
    Dim white As Long
    white = RGB(255, 255, 255)
    
    Dim grey As Long
    grey = RGB(200, 200, 200)
    
    Dim c As Range
    For Each c In r
        If c.Value2 = 1 Then c.Interior.Color = white
        If c.Value2 = 0 Then c.Interior.Color = grey
      
    Next

End Sub

As Ike mentions for Empty values you can use this

Sub SetColor()
    Dim r As Range
    Set r = ThisWorkbook.ActiveSheet.Range("B2:B7")
    
    Dim white As Long
    white = RGB(255, 255, 255)
    
    Dim grey As Long
    grey = RGB(200, 200, 200)
    
    Dim c As Range
    For Each c In r
        If IsEmpty(c.Value2) Then
            c.Interior.Color = white
            'OR
            'c.Interior.Pattern = xlNone
        Else
            c.Interior.Color = grey
      End If
    Next
End Sub
user_121895680
Related