Excel VBA - 根据值更改单元格颜色

Question

在指定范围内的表格中，我想将具有值的单元格标记为白色，将其他没有值的单元格标记为灰色。我有代码，但它不会产生任何结果。也没有错误。需要更改什么才能使其正常工作

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

Answer 1

请运行下一个代码。它会自动将条件格式放置在您需要的范围内:

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

当单元格为空或不为空时，它将使范围单元格自动更改其内部颜色。