Formatting macro

[jbb16x99]

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.

 

[pfsardella]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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