Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Can someone help with this macro?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 74
Default Can someone help with this macro?
I'm trying to apply conditioning formatting to different ranges, but when I
run the macro, it only applies it to the last range.

I hope it makes sense

Sub subtotal()
'
' subtotal Conditional
'

'
Range("G9").Select
Cells.FormatConditions.Delete
Range("G9:G200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("L9").Select
Cells.FormatConditions.Delete
Range("L9:L200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("Q9").Select
Cells.FormatConditions.Delete
Range("q9:q200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=P9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("V9").Select
Cells.FormatConditions.Delete
Range("v9:v200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AA9").Select
Cells.FormatConditions.Delete
Range("AA9:AA200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Z9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AF9").Select
Cells.FormatConditions.Delete
Range("AF9:AF200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AE9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("B2").Select
End Sub







Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,805
Default Can someone help with this macro?
Just remove
Cells.FormatConditions.Delete
everywhere it occurs...

"Gilbert" wrote:

I'm trying to apply conditioning formatting to different ranges, but when I
run the macro, it only applies it to the last range.

I hope it makes sense

Sub subtotal()
'
' subtotal Conditional
'

'
Range("G9").Select
Cells.FormatConditions.Delete
Range("G9:G200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("L9").Select
Cells.FormatConditions.Delete
Range("L9:L200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("Q9").Select
Cells.FormatConditions.Delete
Range("q9:q200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=P9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("V9").Select
Cells.FormatConditions.Delete
Range("v9:v200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AA9").Select
Cells.FormatConditions.Delete
Range("AA9:AA200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Z9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AF9").Select
Cells.FormatConditions.Delete
Range("AF9:AF200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AE9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("B2").Select
End Sub







Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 74
Default Can someone help with this macro?
Very nice Sheeloo. It worked great. Thanks for your help.

"Sheeloo" wrote:

Just remove
Cells.FormatConditions.Delete
everywhere it occurs...

"Gilbert" wrote:

I'm trying to apply conditioning formatting to different ranges, but when I
run the macro, it only applies it to the last range.

I hope it makes sense

Sub subtotal()
'
' subtotal Conditional
'

'
Range("G9").Select
Cells.FormatConditions.Delete
Range("G9:G200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=F9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("L9").Select
Cells.FormatConditions.Delete
Range("L9:L200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=K9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("Q9").Select
Cells.FormatConditions.Delete
Range("q9:q200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=P9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("V9").Select
Cells.FormatConditions.Delete
Range("v9:v200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=U9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AA9").Select
Cells.FormatConditions.Delete
Range("AA9:AA200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=Z9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("AF9").Select
Cells.FormatConditions.Delete
Range("AF9:AF200").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AE9=1"

Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("B2").Select
End Sub







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 09:31 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 08:22 PM
Macro needed to Paste Values and prevent Macro operation thunderfoot Excel Discussion (Misc queries) 0 June 10th 05 03:38 PM


All times are GMT +1. The time now is 02:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement