Databases Reference
In-Depth Information
FIGURE 4.17
Conditional formatting example GUI.
highlights the rows that contain information about the default vehicle voltage scheme (12 V). The code
that generates this formatting is as follows:
Private Sub Button_Default_Click()
Dim row As Integer, col As Integer
Dim cellrange$, formatformula$
For row = 2 To 8
For col = 1 To 3
cellrange$ = Chr(64 + col) & Trim(str(row))
formatformula$ = "=IF($A" & Trim(str(row)) &
"=12,TRUE,FALSE)=TRUE"
Call ApplyCondFormat(FormatBook, ActiveSheet.Name,
Range(cellrange$), formatformula$, 6)
Next col
Next row
End Sub
Looking at the foregoing snippet of code, notice that a
For/Next
looping structure is utilized
to build a string that represents the cell range and cell formatting formula to be utilized when the
ApplyCondFormat
subroutine is called.
The trouble arises when trying to code a VBA routine to apply a conditional formatting routine,
which utilizes a custom function. Recall that such functions may not reside in another Workbook
or Worksheet and must be native to the Workbook or Worksheet they will be utilized in. One
potential workaround for this problem is to construct templates that have the required custom
functions contained within them as a code module. This is done with the Workbook template
“DynamicFormat.xlt,” which contains the function
FormulaInRange
.
The button titled “Show Missing Formula”
should
add a conditional reference using the
FormulaInRange
function to highlight a cell in red to indicate when it contains a static value
instead of a formula. Such an indicator is very useful because static values will not automatically
update should other parameters in the Worksheet be altered. A bug within the Excel VBA framework,
however, prevents this code from executing properly. The code to attempt to execute this formatting
is as follows:
Private Sub Button_Formula_Click()
Dim row As Integer, col As Integer
Dim cellrange$, formatformula$
