Setting Up Conditional Formats in VBA

In the previous example, you saw that the workbook made very liberal use of cell interior colors. We needed a way to turn the font to light blue when an error occurred in a light blue cell, and to turn the font to light yellow when an error occurred in a yellow cell. A quick macro in VBA was the perfect way to do this.

The FormatConditions object is used to set up conditional formats. Because each cell can have three FormatConditions, the following code first deletes all the existing conditional formats on the sheet. The code then loops through all non-blank cells in the worksheet and applies two conditional formats. In the first conditional format, the type is xlExpression, which means we are using the Formula Is syntax. Note that the formula specified for Formula1 is in R1C1-style notation. The second conditional format uses the xlCellValue type, which requires both specifying an operator and a value. After we've added the condition, we set the Colorlndex for the font for conditions 1 and 2:

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

0 0

Post a comment