Selection.FormatConditions

[blbat]

Is there a cleaner, faster way to for my code to take this:

Excel Workbook
	G
1	Days Open
2	2
3	10
4	11
5	11
6	11
7	11
8	15
9	15
10	15
11	21
12	22
13	23
14	23
15	24
16	29
17	67
18	87
19	95
20	105
working

Excel 2007


and with click of a button get it conditionally formatted as follows:
(This code works, I was just looking for a better way)

Private Sub cmdDaysOpn_Click()
    Dim lLastRow As Long
 
    With ActiveSheet
         'find the last row in the worksheet
        lLastRow = Get_Last_Row(.Cells)
    End With
 
    'Select the Used Range in column G
    With ActiveSheet.Range("G2:G" & lLastRow).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    With Selection
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=89"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
 
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
 
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=60", Formula2:="=89"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 27
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=60"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 4
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
        End With
    End With
End Sub

 

[pbornemeier]

Only a minor changes:
1. You don't need to select the area you are working on.
2. You should always remove any previous conditions. With Excel 2007 and greater, you can have more than three conditions and without removing the old ones, you would get multiple overlapping conditions.
3. The last used row for the entire worksheet and the last used row for column G may differ, so I used a method to determine the last used cell in that range.

Private Sub cmdDaysOpn_Click()
    Dim lLastRow As Long
 
    lLastRow = Cells(Rows.Count, 7).End(xlUp).Row
 
    With ActiveSheet.Range("G2:G" & lLastRow)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
 
        .FormatConditions.Delete 'Clear any existing conditional formats
 
        'Red
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
            Formula1:="=89"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Font
            .Color = -16383844
            .TintAndShade = 0
        End With
 
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
 
        'Yellow
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
            Formula1:="=60", Formula2:="=89"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 27
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
 
         'Green
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
            Formula1:="=60"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 4
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
 
End Sub

 

[blbat]

pbornemeier,

I appreciate the feedback.

I had to look close to see why I don't need to select the range-
Thanks for that!

Last Row- for this particular spreadsheet the last row will always be the same across the columns, but that is a excellent method to determine last used cell and I will remember it.

Clearing previous FormatConditions- I did not think about that and it might have caused some strange results. Thanks for that line as well.

regards,

blbat