ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Can someone help with this macro? (https://www.excelbanter.com/excel-discussion-misc-queries/223965-can-someone-help-macro.html)

GILBERT
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

Sheeloo[_3_]
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

GILBERT
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


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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com