View Single Post
 
Old 01-25-2019, 03:46 PM
slouw slouw is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Mar 2016
Posts: 4
slouw is on a distinguished road
Default Conditional formatting using specified range instead of With Selection statement

Why does this work when I have only a single FormatConditions(1) statement:

Code:
'   Single FormatConditions(1) statement
 '   Why does this work
Sub CF_NO_with()
'                            Row 5
'                               Column1
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions.Delete
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(column(),2)=0"
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).Interior.ColorIndex = 15
End Sub
But this this fails when I have multiple FormatConditions(1) statements:

Code:
'   Multiple FormatConditions(1) statement
'   Why does this NOT work ((if you have multiple FormatConditions(1) statements?))
Sub CF_NO_with()
'                            Row 5
'                               Column1
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions.Delete
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(column(),2)=0"
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).Interior.ColorIndex = 15
    ' Works up to here. Next line is an error why?
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).PatternColorIndex = 15
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).Pattern = xlGray75

End Sub
This is my control script which does work:
Code:
' This works ((Control)
Sub CF_with()
    Range(Cells(303, 5), Cells(308, 24)).FormatConditions.Delete
    Range(Cells(303, 5), Cells(308, 24)).Select
    With Selection
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(column(),2)=0"
        With .FormatConditions(1).Interior
            .ColorIndex = 15
            .PatternColorIndex = 15
            .Pattern = xlGray75
        End With
    End With
 End Sub
Reply With Quote