Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting macro
I'm relatively new to excel macros so bare with me. I've written a
simple formatting macro that I want to enhance. The macro is as below. Sub CondFormat() Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, _ Formula1:="=+$A$1" Selection.FormatConditions(1).Font.ColorIndex = 2 Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, _ Formula1:="=-$A$1" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("A1").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub The macro fails and gives me an error message if I run it on cells already containing the conditional formatting. I'm not sure why. So, I'd like to make the macro check first to see if this conditional formatting already exists. If it does, then I want the macro to delete the conditional formatting. If it doesn't already exist, then execute the conditional formatting. Additionally, no matter if this conditional formatting already exists or not, I'd like to always color cell A1 with ColorIndex 6. Can someone help? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting macro
Sub CondFormat()
With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, Formula1:="=+$A$1" .FormatConditions(1).Font.ColorIndex = 2 .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, Formula1:="=-$A$1" .FormatConditions(2).Font.ColorIndex = 2 .FormatConditions(2).Interior.ColorIndex = 3 End With With Range("A1").Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub HTH Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting macro
Just delete existinf formatting, that is
Sub CondFormat() With Selection .FormatConditions.Delete .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, _ Formula1:="=+$A$1" .FormatConditions(1).Font.ColorIndex = 2 .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, _ Formula1:="=-$A$1" .FormatConditions(2).Font.ColorIndex = 2 .FormatConditions(2).Interior.ColorIndex = 3 End With With Range("A1").Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jbb16x99" wrote in message om... I'm relatively new to excel macros so bare with me. I've written a simple formatting macro that I want to enhance. The macro is as below. Sub CondFormat() Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, _ Formula1:="=+$A$1" Selection.FormatConditions(1).Font.ColorIndex = 2 Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, _ Formula1:="=-$A$1" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("A1").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub The macro fails and gives me an error message if I run it on cells already containing the conditional formatting. I'm not sure why. So, I'd like to make the macro check first to see if this conditional formatting already exists. If it does, then I want the macro to delete the conditional formatting. If it doesn't already exist, then execute the conditional formatting. Additionally, no matter if this conditional formatting already exists or not, I'd like to always color cell A1 with ColorIndex 6. Can someone help? Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting macro
Sub CondFormat() On Error Resume Next Selection.formatconditions.Delete On Error goto 0 Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, _ Formula1:="=+$A$1" Selection.FormatConditions(1).Font.ColorIndex = 2 Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, _ Formula1:="=-$A$1" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("A1").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub -- Regards, Tom Ogilvy jbb16x99 wrote in message om... I'm relatively new to excel macros so bare with me. I've written a simple formatting macro that I want to enhance. The macro is as below. Sub CondFormat() Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlGreater, _ Formula1:="=+$A$1" Selection.FormatConditions(1).Font.ColorIndex = 2 Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, _ Operator:=xlLess, _ Formula1:="=-$A$1" Selection.FormatConditions(2).Font.ColorIndex = 2 Selection.FormatConditions(2).Interior.ColorIndex = 3 Range("A1").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(myrange).Select End Sub The macro fails and gives me an error message if I run it on cells already containing the conditional formatting. I'm not sure why. So, I'd like to make the macro check first to see if this conditional formatting already exists. If it does, then I want the macro to delete the conditional formatting. If it doesn't already exist, then execute the conditional formatting. Additionally, no matter if this conditional formatting already exists or not, I'd like to always color cell A1 with ColorIndex 6. Can someone help? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formatting macro help | Excel Discussion (Misc queries) | |||
Formatting using Macro | Excel Discussion (Misc queries) | |||
Formatting for a Macro | Excel Discussion (Misc queries) | |||
Formatting via a macro | Excel Discussion (Misc queries) | |||
Macro for formatting | Excel Programming |