Databases Reference
In-Depth Information
For row = 2 To 8
cellrange$ = "C" & Trim(str(row))
formatformula$ = "=FormulaInRange(" & cellrange$ & ")=False"
Call ApplyCondFormat(FormatBook, ActiveSheet.Name,
Range(cellrange$), formatformula$, , True, , 3, , 2)
Next row
End Sub
The unfortunate thing that happens with this code is that the conditional formatting function appears
to be applied ok to cell C2; however, when the code reaches cell C3, it abnormally terminates. Cell C3
contains the conditional formatting function, but no specified formatting will be applied should the
condition prove true (Figure 4.18). Cells C4-C8 then have no conditional formatting applied will them.
Even more strange is that, although the correct formatting appears to be applied in cell C2, it will not
function correctly if the value in cell C2 is changed from a formula to a static value (Figure 4.18).
Finally, the “Clear Formatting” button erases all of the formatting that may have been applied
to the region of cells in the Worksheet that contain data. The code to accomplish this is
Range("A1:C8").Select
Selection.FormatConditions.Delete
This section would not be complete without a method to automate the format painter tool shown
in Figure 4.12. Here the conditional formatting statements present in the first range are copied into
the second range. For example, here, the conditional formatting statements present in cell A1 will
be copied over the range of A1:H30.
Call PaintFormats(Range("A1"),Range("A1:H30"))
The subroutine which accomplishes this is:
Sub PaintFormats(ByVal CellWithFormat As Range, ByVal
FormatArea As Range)
'This subroutine will paint formats from a given cell to a
Range of cells
CellWithFormat.Select
Selection.Copy
FormatArea.Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone
End Sub
FIGURE 4.18
Abnormal termination in VBA code results in unset formats.
