Excel VBA - Change cell color based on value-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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