Paste a conditional format without replacing existing formats??

[Guest]

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

 

[Guest]

Thanks Doug, I did a similar thing with the macro recorder and got the first
part sorted...

Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=($I1=""BPC"")"
Selection.FormatConditions(1).Interior.ColorIndex = 36
Selection.Copy
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select

My macro then inserts formulas into various columns in row 2 and copies
these down to nRows - I then applied the second layer of formatting by 'add'
in conditional format box but my red font doesn't work in cells already
highlighted yellow. It works for cells with no other format. Do you know of a
way to get the red font on the yellow background??? I'm sure I've seen this
working before...

Thanks

 

[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

 

[Guest]

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

 

[Guest]

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