Databases Reference
In-Depth Information
Case True
'Search Entire Worksheet
colstpt = 1: colmax = LastCol(ActiveWorkbook.Name,
ActiveSheet.Name)
rowmax = LastRow(ActiveWorkbook.Name, ActiveSheet.Name)
Case False
'Just Search Specified colstptumn
colstpt = Asc(TextBox_Col.Text) - 64: colmax =
Asc(TextBox_Col.Text) - 64
rowmax = LastRow(ActiveWorkbook.Name, ActiveSheet.Name)
End Select
For row = 1 To rowmax
For col = colstpt To colmax
If
CheckCondition
(Cells(row,
col), Compare) = True Then
Cells(row, col).Select
With Selection.Interior
.Color = Colors(ColorComboBox.ListIndex + 1, 2)
.Pattern = xlSolid
End With
End If
Next col
Next row
End Sub
Prior to executing, this subroutine makes some preliminary checks to determine if the user has
made selections for all the necessary parameters prior to executing the highlighting algorithm.
Although it requires some extra effort to incorporate such error-detection mechanisms, it is well
worth the effort because it prevents both errors and unexpected results from occurring. Specifically,
the subroutine checks for the following items prior to execution:
1.
If the RefEdit control was to be utilized to pick a comparison value from the Worksheet,
was only a single cell selected?
2.
If a Comparison Value needs to be entered on the GUI, was this done?
3.
Was a valid Comparison Operator chosen?
4.
Was a color chosen for highlighting the background of those cells that met the specified criteria?
If all of these were chosen, then the macro will highlight the cells that meet the specified criteria.
This is done by means of a looping structure. Prior to beginning the loop, a determination is made if
the comparison operation should be made throughout the entire Worksheet or just a single column,
and the looping parameters are set accordingly. Notice that the comparison operator is chosen by
means of a Combo box. This makes writing a comparison statement problematic, as many different
statements must be written depending upon the number of comparison operators. For clarity's sake,
a function has been created to return a Boolean value of true or false depending upon if the condition
tested for proves true. This greatly improves the readability of the Button_Highlight_Click subroutine.
Function CheckCondition(CellValue, CompareValue) As Boolean
Select Case ComboBox_Cond1.Text
