Removed data fields breaking my code

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448
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:
Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
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
 

Forum statistics

Threads
1,136,652
Messages
5,677,007
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top