Re: [ExcelVBA] Re: Formatting with Macro

 

Heya Steve,

Quite a bit there!

Have you thought about building a "loop" and using Ranges instead of selections ... and it would probably run faster if you turnd off calculation and screen updating while running.

HTH
Lisa

Sent: Sun, Apr 15, 2012 2:14 pm
Subject: [ExcelVBA] Re: Formatting with Macro

Had in a flash drive...here goes:

ub AddConditionalFormatToWks()

Add Conditional Format To Worksheets Macro

Cells.FormatConditions.Delete

Range("F2:F20001").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = True

Range("F2:F20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False

Range("G2:G20001").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = True

Range("G2:G20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False

Range("H2:H20001").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = True

Range("H2:H20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False

Range("I2:I20001").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = True

Range("I2:I20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False

Range("J2:J20001").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$B$2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=$K$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).StopIfTrue = True

Range("J2:J20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False

Range("A2:A20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False



Range("B2:B20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False



Range("C2:C20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False



Range("D2:D20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False



Range("E2:E20001").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A2))>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlContinuous
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlContinuous
Selection.FormatConditions(1).StopIfTrue = False


Range("A2").Select
End Sub

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (15)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___