Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-25-2019, 03:46 PM
slouw slouw is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Mar 2016
Posts: 3
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
  #2  
Old 01-26-2019, 01:22 AM
alpha alpha is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 15
alpha is on a distinguished road
Default

Quote:
Next line is an error why?
Code:
Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).PatternColorIndex = 15
Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).Pattern = xlGray75
Must be:
Code:
Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).interior.PatternColorIndex = 15
Range(Cells(303, 5), Cells(308, 24)).FormatConditions(1).interior.Pattern = xlGray75
Reply With Quote
  #3  
Old 01-26-2019, 03:49 AM
slouw slouw is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Mar 2016
Posts: 3
slouw is on a distinguished road
Default Thank you sir!

Doh!!!
Thank you much appreciated....
Reply With Quote
  #4  
Old 01-26-2019, 06:24 AM
alpha alpha is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 15
alpha is on a distinguished road
Default

You're welcome !
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Individual Rows in a range Phil H Excel 3 11-05-2018 12:09 PM
Conditional Formatting - Duplicates - Apply to specified range music_al Excel Programming 1 01-25-2017 05:34 AM
Conditional Formatting a Range Phil H Excel 3 02-17-2016 08:33 AM
VBA Conditional Formatting Error (Subscript out of Range)) tinfanide Excel Programming 1 05-15-2015 08:12 AM
Conditional Formatting/If Statement with Dates AndrewEnos Excel 2 07-22-2014 07:34 AM


All times are GMT -7. The time now is 05:52 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft