VBA批量添加条件格式

工作上有这样的需求,在工作表B2单元格输入数量,在F2单元格如果为空,就呈现橙色提醒输入人注意输入板材类型,当我在F2单元格输入“条件格式”,在单元格右下角往下拉使用“自动填充”功能时,并不能达到我希望的效果,只能上VBA解决。

Sub AddCondition()
Dim wsName As String
Dim targetWs As Worksheet
Dim row As Integer
Dim sheetIndex As Integer
'从工作表10循环到30
For sheetIndex = 10 To 30
  wsName = "4月" & sheetIndex & "日"
  On Error Resume Next
  Set targetWs = Worksheets(wsName)
  On Error GoTo 0
  
  If Not targetWs Is Nothing Then
    For row = 2 To 51
      With targetWs.Range("$F$" & row).FormatConditions
      .Delete
      .Add Type:=xlExpression, Formula1:="=and($B$" & row & "<>"""",$F$" & row & "="""")"
      .Item(1).Interior.Color = RGB(255, 165, 0)
      End With
    Next row
  End If
Next sheetIndex
MsgBox "运行结束"

End Sub

达到我希望的效果。

posted @ 2025-04-10 16:18  孤独的小苗  阅读(26)  评论(0)    收藏  举报