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
达到我希望的效果。