Conditional formatting within macro?
 

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?

Thanks.

Conditional formatting within macro?
 

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).SetFirs tPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
End With


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Doh! I appreciate your help :)

J-

Conditional formatting within macro?
 

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).SetFir st 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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

The first one worked perfectly. Thank you!!!!

J-

Conditional formatting within macro?
 

Hi Claus,
I need to put a Formula in an expression as you showed above.
My issue is that I need manage the columns as a number and not a letter. Ranges have to be dynamic in columns.

If I write:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SUM($I$5:$N$5)0"

it works properly, but

Selection.FormatConditions.Add Type:=xlExpression, FormulaR1C1:="=SUM(R5C9:R5C14)0"

it doesn't.

Can you give me any help on it?
Thx

Conditional formatting within macro?
 

Hi,

Am Fri, 10 Jul 2015 03:31:43 -0700 (PDT) schrieb
:

I need to put a Formula in an expression as you showed above.
My issue is that I need manage the columns as a number and not a letter. Ranges have to be dynamic in columns.

try it this way:

Dim LCol As Long, LRow As Long

With ActiveSheet
LCol = .UsedRange.Columns.Count
LRow = .UsedRange.Rows.Count
.Range(.Cells(5, 9), .Cells(LRow, LCol)).Select
End With

With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=SUM(I5:" & Cells(5, LCol).Address(0, 0) & ")0"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting within macro?
 

Hi Claus,
many thanks for your answer.

Turning the Formula type from FormulaR1C1 to Formula! and using ".Address(0,0)" at least no error is returned.

But now the CF is not effective (cells painted when the condition is true) even I can see the rule set on the CF Manager.

Conditional formatting within macro?
 

Hi,

Am Fri, 10 Jul 2015 05:57:14 -0700 (PDT) schrieb
:

Turning the Formula type from FormulaR1C1 to Formula! and using ".Address(0,0)" at least no error is returned.

But now the CF is not effective (cells painted when the condition is true) even I can see the rule set on the CF Manager.

try instead:
With Selection
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=Sum($I5:" & Cells(5, LCol).Address(0, 1) & ")0"

What language version do you use? Formula1 demands on function names in
your language.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting within macro?
 

Hey Claus,
thanks for your help, now it works great.

The point was the clause ".Address(0/1,0/1)" which lets me put the $'s wherever is needed.
I fixed my issue by:

..FormatConditions.Add Type:=xlExpression, Formula1:="=SUMA(" & Cells(5, 9).Address(1, 1) & ":" & Cells(5, 14).Address(1, 1) & ")0"

where columns now are available to be managed as numbers, thus variables.

Regarding the language, my excel is Spanish. I had no need to use "FormulaLocal", what you see above is enough.

Thanks again,
Jordi