[ExcelVBA] Re: Formatting with Macro

 

I really don't know much about how to build a loop. However, I can turn the calculation off while its running, just didn't think of it.

Thanks,

Steve

--- In ExcelVBA@yahoogroups.com, 1z@... wrote:
>
>
> 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 (16)

Recent Activity:

• New Members 7

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

.

__,_._,___