Paste a conditional format without replacing existing formats??

[Meltad]

Hi,

My worksheet has various conditional formatting.
The whole sheet is formatted to change the row fill colour to yellow if BPC
appears in column I.
But other columns also contain various seperate conditional formatting which
turn the font colour red.
Is there a way of applying both these formats to the same cell using my macro.
EG can I first apply the yellow row formatting to the whole sheet then ADD
the seperate formats afterwards (can't copy and paste the formats from my
teplate sheet as this would overwrite)...

Help!

 

[Doug Glancy]

Meltad,

Starting the Macro Recorder and defining two conditional formats for A1 this
is what I get. The first format turns it yellow if a cell in column I
contains "BPC". The second turns it red if it's equal to 0. Not quite what
you wanted, but it might help:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($I:$I,""BPC"")>0"
Selection.FormatConditions(1).Interior.ColorIndex = 27
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(2).Interior.ColorIndex = 3

hth,

Doug

 

[Doug Glancy]

Meltad,

Conditional formatting stops applying formatting after a condition is true,
i.e., if the first condition is true, it doesn't even evaluate the second
condition.

This means you need to apply the more restrictive, complex formatting first.
E.g., if you want cells that have a 0 in them AND have "BPC" in column I of
the same row, then you have to use an "AND" formula that captures both of
these. Your format would include both the yellow background and red font.

You could then have a second condition - less restrictive - that addresses
the condition where only column I has "BPC" and just turn the background
yellow:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($I1=""BPC"",$A1=0)"
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$I1=""BPC"""
Selection.FormatConditions(2).Interior.ColorIndex = 6

I hope that helps,

Doug

 

[Meltad]

That makes sense, thanks a lot Doug. I'll try his out on Monday and let you
know how it goes!

 

[Meltad]

Thanks Doug, this works great

Meltad,

Conditional formatting stops applying formatting after a condition is true,
i.e., if the first condition is true, it doesn't even evaluate the second
condition.

This means you need to apply the more restrictive, complex formatting first.
E.g., if you want cells that have a 0 in them AND have "BPC" in column I of
the same row, then you have to use an "AND" formula that captures both of
these. Your format would include both the yellow background and red font.

You could then have a second condition - less restrictive - that addresses
the condition where only column I has "BPC" and just turn the background
yellow:

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($I1=""BPC"",$A1=0)"
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$I1=""BPC"""
Selection.FormatConditions(2).Interior.ColorIndex = 6

I hope that helps,

Doug