Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

Setting Up Conditional Formats in VBA

Sub ApplySpecialFormattingAll()     For Each ws In ThisWorkbook.Worksheets         ws.UsedRange.FormatConditions.Delete         For Each cell In ws.UsedRange.Cells             If Not IsEmpty(cell) Then                 cell.FormatConditions.Add Type:=xlExpression, _                     Formula1:="=or(ISERR(RC),isna(RC))"                 cell.FormatConditions(1).Font.Color = cell.Interior.Color                 cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _                     Formula1:="0"                 cell.FormatConditions(2).Font.ColorIndex = 3             End If         Next cell     Next ws End Sub