Removed data fields breaking my code

[gingerafro]

I have a worksheet event macro that performs a conditional format on certain data fields in a pivot table.

However, if a user removes the field, the macro errors because it doesn't exist.

What I need is a 'if the field isn't there, skip this bit of code'

The current code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Cells.Select
    Selection.FormatConditions.Delete
    
ActiveSheet.PivotTables("PivotTable1").PivotSelect "AVAILABILITY", _
        xlDataAndLabel, True
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="0"
    Selection.FormatConditions(1).Interior.ColorIndex = 3

ActiveSheet.PivotTables("PivotTable1").PivotSelect "% AVAILABLE", _
       xlDataAndLabel, True
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="0.0002", Formula2:="0.4"
    Selection.FormatConditions(1).Interior.ColorIndex = 3

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="0.4001", Formula2:="0.7"
    Selection.FormatConditions(2).Interior.ColorIndex = 6
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="0.0001"
    Selection.FormatConditions(3).Font.ColorIndex = 2
Range("A1").Select

End Sub

 

[Richard Schollar]

Hey

How about an On Error Resume Next in your code?

Richard

 

[awalle]

Have you tryed to use the statment:

"On Error Resume Next" ?

This statement should be use in the prior line to the line you are having the error, when any error ocurrs this will process the next line.

Or you can create an error-catch routine using the same statement but direct it to an error routine. ("On error resume error-catching-routine").


Hope this can help you.

AWalle

 

[gingerafro]

dammit, I knew that!

Thank you for your help again mate