Conditional formatting within macro?

[JonathanK1]

I know how to use conditional formatting within the excel sheet, but
want to integrate it into a macro (the macro is doing other things a
well). Is this possible?

Basically, there is a column for the age of a car. The macro is pullin
the data and pasting it into a new workbook - but when it's pasted/a
it's pasted, I want anything older than 2008 (age in column Q) to becom
red (the cell that is). Is this possible?

Thanks

 

[Claus Busch]

Hi Jonathan,

Am Tue, 26 Mar 2013 14:39:17 +0000 schrieb JonathanK1:

I know how to use conditional formatting within the excel sheet, but I
want to integrate it into a macro (the macro is doing other things as
well). Is this possible?

Basically, there is a column for the age of a car. The macro is pulling
the data and pasting it into a new workbook - but when it's pasted/as
it's pasted, I want anything older than 2008 (age in column Q) to become
red (the cell that is). Is this possible?

implement to your existing code:

Dim LRow As Long

LRow = Cells(Rows.Count, "Q").End(xlUp).Row
Range("$Q$2:$Q$" & LRow).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$Q2<2008"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
End With


Regards
Claus Busch

 

[JonathanK1]

Hi Jonathan,

Am Tue, 26 Mar 2013 14:39:17 +0000 schrieb JonathanK1:
-

implement to your existing code:

Dim LRow As Long

LRow = Cells(Rows.Count, "Q").End(xlUp).Row
Range("$Q$2:$Q$" & LRow).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$Q2<2008"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
End With


Regards
Claus Busch

It acted like it was going to work and then stopped, highlighting th
".FormatConditions(.FormatConditions.Count).SetFirst Priority" section


Doh! I appreciate your help

J

 

[Claus Busch]

Hi Jonathan,

Am Wed, 27 Mar 2013 10:45:27 +0000 schrieb JonathanK1:

It acted like it was going to work and then stopped, highlighting the
".FormatConditions(.FormatConditions.Count).SetFirst Priority" section.

do you have other conditional formattings in your sheet? What Excel
version do you use?
Try:

Sub test()
Dim LRow As Long

LRow = Cells(Rows.Count, "Q").End(xlUp).Row
Range("$Q$2:$Q$" & LRow).Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=$Q2<2008"
With .FormatConditions(1).Interior
.ColorIndex = 3
End With
End With
End Sub

and if it doesn't work, try:

Sub test2()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, "Q").End(xlUp).Row
For Each rngC In Range("$Q$2:$Q$" & LRow)
rngC.Interior.ColorIndex = IIf(rngC < 2008, 3, xlNone)
Next
End Sub


Regards
Claus Busch