| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste a conditional format without replacing existing formats??
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! :-) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste a conditional format without replacing existing formats??
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 "Meltad" wrote in message ... 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! :-) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste a conditional format without replacing existing formats?
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" wrote: 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 "Meltad" wrote in message ... 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! :-) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste a conditional format without replacing existing formats?
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" wrote in message ... 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" wrote: 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 "Meltad" wrote in message ... 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! :-) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste a conditional format without replacing existing formats?
That makes sense, thanks a lot Doug. I'll try his out on Monday and let you
know how it goes! "Doug Glancy" wrote: 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" wrote in message ... 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" wrote: 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 "Meltad" wrote in message ... 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! :-) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Paste a conditional format without replacing existing formats?
Thanks Doug, this works great
"Doug Glancy" wrote: 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" wrote in message ... 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" wrote: 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 "Meltad" wrote in message ... 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! :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Keeping Conditional Formats 'Constant' when copy & paste | Excel Discussion (Misc queries) | |||
| Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
| paste conditional formats as formats | Excel Discussion (Misc queries) | |||
| Paste Special Formats Generated by Conditional Formatting | Excel Discussion (Misc queries) | |||
| Paste Conditional Formats | Excel Programming | |||
Linear Mode
