Microsoft Office Forums Conditional formatting using specified range instead of With Selection statement

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-25-2019, 03:46 PM
slouw slouw is offline Conditional formatting using specified range instead of With Selection statement Windows 7 64bit Conditional formatting using specified range instead of With Selection statement Office 2010 64bit
Novice
Conditional formatting using specified range instead of With Selection statement
 
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 Conditional formatting using specified range instead of With Selection statement Windows 10 Conditional formatting using specified range instead of With Selection statement Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 18
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 Conditional formatting using specified range instead of With Selection statement Windows 7 64bit Conditional formatting using specified range instead of With Selection statement Office 2010 64bit
Novice
Conditional formatting using specified range instead of With Selection statement
 
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 Conditional formatting using specified range instead of With Selection statement Windows 10 Conditional formatting using specified range instead of With Selection statement Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 18
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 using specified range instead of With Selection statement Conditional Formatting Individual Rows in a range Phil H Excel 3 11-05-2018 12:09 PM
Conditional formatting using specified range instead of With Selection statement 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 07:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft