runtime error macro

[plost33]

I have the following macro in 03 excel and Witht the sheet protected I am getting a run time error '1004': unable to set the NumberFormat property of the Range class. when debugged it highlights:

.NumberFormat = "hh:mm"


here is the entire macro code:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("D12:D36")) Is Nothing Then
Application.EnableEvents = False
Select Case Target.Value
Case "Weather-NP": Target.Value = "WOW-NP"
Case "Bell Run-NP": Target.Value = "BR-NP"
Case "Survey/Diagnostics-A": Target.Value = "SD-A"
Case "Diamond Wire Cutter-A": Target.Value = "DWC-A"
Case "Diver Survey-A": Target.Value = "DS-A"
Case "Chop Saw-A": Target.Value = "CS-A"
Case "Deck Removal-A": Target.Value = "DR-A"
Case "Structure Removal-A": Target.Value = "SR-A"
Case "Excavate-A": Target.Value = "EXC-A"
Case "Strongback-I": Target.Value = "SB-I"
Case "Excavate-I": Target.Value = "EXC-I"
Case "Survey/Diagnostic-I": Target.Value = "SD-I"
Case "Annular Interval Tester-I": Target.Value = "AIT-I"
Case "Diver Survey-I": Target.Value = "DS-I"
Case "Conventional Hot Tap-I": Target.Value = "CHT-I"
Case "Direct Hot Tap-I": Target.Value = "DHT-I"
Case "Bleed & Kill-I": Target.Value = "BK-I"
Case "Shear Operation-I": Target.Value = "SO-I"
Case "Diamond Wire Cutter-I": Target.Value = "DWC-I"
Case "Chop Saw-I": Target.Value = "CS-I"
Case "Porta-Lathe-I": Target.Value = "PL-I"
Case "Wedding Cake-I": Target.Value = "WC-I"
Case "Install Well Head-I": Target.Value = "IWH-I"
Case "Survey/Diagnostic-TA": Target.Value = "SD-TA"
Case "Test Surf/Prod. Annulus-TA": Target.Value = "TCA-TA"
Case "Wireline Bottom-TA": Target.Value = "WL-B"
Case "Establish Injection/Circulation Bottom-TA": Target.Value = "EIR-B"
Case "Perf Bottom-TA": Target.Value = "PERF-B"
Case "Cement Bottom Plug-TA": Target.Value = "CMT-B"
Case "Test CMT Bottom Plug-TA": Target.Value = "TCP-B"
Case "Wireline Intermediate-TA": Target.Value = "WL-I"
Case "Establish Injection/Circulation Intermediate-TA": Target.Value = "EIR-I"
Case "Perf Intermediate-TA": Target.Value = "PERF-I"
Case "Cement Intermediate Plug-TA": Target.Value = "CMT-I"
Case "Diver Survey-TA": Target.Value = "DS-TA"
Case "Wireline Surface-TA": Target.Value = "WL-S"
Case "Establish Injection/Circulation Surface-TA": Target.Value = "EIR-S"
Case "Perf Surface-TA": Target.Value = "PERF-S"
Case "Cement Surface Plug-TA": Target.Value = "CMT-S"
Case "Structure Removal-PA": Target.Value = "SR-PA"
Case "Install Tester-TA": Target.Value = "AIT-TA"
Case "Grout Csg Annulus-TA": Target.Value = "GCA-TA"
Case "Cut & Pull Csg-PA": Target.Value = "CPC-PA"
Case "Debris Clearance-PA": Target.Value = "DC-PA"
Case "Survey/Diagnostic-PA": Target.Value = "SD-PA"
Case "Deck Removal-PA": Target.Value = "DR-PA"
Case "Diver Survey-PA": Target.Value = "DS-PA"
Case "Mesotech-PA": Target.Value = "MESO-PA"
Case "Trawling Site Clearance-PA": Target.Value = "TSC-PA"
Case "Exit Survey-PA": Target.Value = "ES-PA"
Case "Pipeline Survey-PA": Target.Value = "PS-PA"
Case "Mechanical Downtime-NP": Target.Value = "MDT-NP"
Case "Vessel Downtime-NP": Target.Value = "VDT-NP"
Case "Regulatory-NP": Target.Value = "REG-NP"
Case "Mobe/Demobe-NP": Target.Value = "MDM-NP"
Case "Health Safety Environment-NP": Target.Value = "HSE-NP"
Case "Waiting on Orders-NP": Target.Value = "WOO-NP"
Case "Other-NP": Target.Value = "OTHER-NP"
End Select
Application.EnableEvents = True
ElseIf Not Intersect(Target, Range("E12:F36")) Is Nothing Then
Application.EnableEvents = False
Target.Value = WorksheetFunction.Floor(Target.Value + 7.5 / 1440, 15 / 1440)
If Target.Column = 6 Then
With Target.Offset(1, -1)
.NumberFormat = "hh:mm"
.Value = Target.Value
End With
End If
Application.EnableEvents = True
End If
End Sub




hope someone can help

 

[VoG]

Please stick to your original thread http://www.mrexcel.com/forum/showthread.php?t=354038

 

[Andrew Poulsom]

When you protected the worksheet did you check Format Cells?