Microsoft Office Tutorials and References
In Depth Information
Using More Than Three Conditional Formats
This macro is called from the Worksheet_Change event. It passes through
those cells that have changed to see if they need to be formatted.
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub ConditionalFormat(ByVal Target As Range)¶
'Variable declarations¶
'Range used for the loop¶
Dim Cll As Range¶
'First, check if the range that was received is¶
'between the one that is needed, use A1:A100¶
Set Target = Intersect(Target, Target.Worksheet.Range("A1:A100"))¶
If Target Is Nothing Then¶
'The cells that changed are not in the range, exit¶
Exit Sub¶
Else¶
'Loop through the cells (in case more than one changed)¶
'only check cells that have numbers in them in this case¶
If Target.Count = 1 Then¶
If Not IsNumeric(Target) Then¶
Set Target = Nothing¶
End If¶
Else¶
On Error Resume Next¶
Set Target = Target.SpecialCells(xlCellTypeConstants, _¶
xlNumbers)¶
On Error GoTo 0¶
End If¶
If Target Is Nothing Then¶
'Don't have any numbers, exit¶
Exit Sub¶
End If¶
For Each Cll In Target.Cells¶
'Use Select Case to figure out where the value of¶
'the cell falls¶
'Change the following variables¶
Select Case Cll.Value¶
Case 0 To 15000¶
Cll.Interior.Color = 8388608¶
Case 15000 To 25000¶
Cll.Interior.Color = 16711680¶
Case 25000 To 35000¶
Cll.Interior.Color = 16764057¶
Case 35000 To 50000¶
Cll.Interior.Color = 13408767¶
Case 50000 To 75000¶
Cll.Interior.Color = 255¶
Exl
Search JabSto ::




Custom Search