[ExcelVBA] Re: Formatting with Macro

 

Had in a flash drive...here goes:

Sub 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

--- In ExcelVBA@yahoogroups.com, "sspatriots" <sspatriots@...> wrote:
>
> I can post it on Monday. It's on my work PC. Warning, it is a very long code...
>
> --- In ExcelVBA@yahoogroups.com, Dr John C Bullas <john.bullas@> wrote:
> >
> > Can you post the answer please?
> >
> > Dr B
> >
> > On 14/04/2012, sspatriots <sspatriots@> wrote:
> > > Lisa,
> > >
> > > Well I finally figured out what I was doing wrong after reading your last
> > > post. I was trying to record the macro by going through the 'Conditional
> > > Formatting - Manage Rules' menu to create the macro. It would never record
> > > those steps. Anyhow, tried what you said and after a few hours of
> > > manipulating several other rules everything works much better than I
> > > expected. I had a few problems with the part that kept trying to set one
> > > priority in front of the other...
> > >
> > > Thanks for the help and tips.
> > >
> > >
> > > Regards,
> > >
> > > Steve
> > >
> > > --- In ExcelVBA@yahoogroups.com, 1z@ wrote:
> > >>
> > >>
> > >> Hey Steve,
> > >>
> > >> I just recorded this....
> > >>
> > >> Selection.FormatConditions.AddColorScale ColorScaleType:=2
> > >>
> > >> Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
> > >> Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
> > >> xlConditionValueLowestValue
> > >> With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
> > >> .Color = 2650623
> > >> .TintAndShade = 0
> > >> End With
> > >> Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
> > >> xlConditionValueHighestValue
> > >> With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
> > >> .Color = 10285055
> > >> .TintAndShade = 0
> > >> End With
> > >>
> > >> I opened a new sheet, selected New Rule from Conditional formatting and
> > >> pressed enter/return so everything is default and for A1.
> > >> You can prob googlee/whatever the FormatConditions collection and so on
> > >> for more info.
> > >>
> > >> HTH
> > >> Lisa
> > >>
> > >>
> > >>
> > >>
> > >> Sent: Fri, Apr 13, 2012 2:33 pm
> > >> Subject: [ExcelVBA] Re: Formatting with Macro
> > >>
> > >>
> > >>
> > >> Hi Lisa,
> > >> Not sure I have the ability to put code together for this that implements
> > >> onditioinal formatting of the nature I'm talking about. I still rely on
> > >> ecording macros and then editing to suit. VBA won't let me record
> > >> conditional
> > >> ormatting steps.
> > >>
> > >> hanks,
> > >> Steve
> > >>
> > >>
> > >>
> > >> [Non-text portions of this message have been removed]
> > >>
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > ----------------------------------
> > > 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
> > >
> > > ----------------------------------Yahoo! Groups Links
> > >
> > >
> > >
> > >
> >
> > --
> > Sent from my mobile device
> >
> >
> >
> >
> > ===========================
> > http://uk.linkedin.com/in/drjohnbullas
> >
>

__._,_.___

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

Messages in this topic (14)

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

.

__,_._,___