I am new to vb code and would like help with the following problem.

I am running the following code in a multipage spreadsheet. Columns 5
8 and 11 should be the only unprotected columns in the six sheets i
question. Based upon a formula in columns 6, 9 and 12 of sheets 1 thr
6 and a value in sheet 7 those columns (6,9,12) are set to an "X" valu
or "RANK"ed base upon the values in 5, 8 and 11. Note: some values i
columns and 5 and 6 are null value. This formula works and modifys th
correct cells in columns 6, 9 and 12. The desired effect of the VB cod
below is to increment/decrement cells in formula selected rows by 100
to so the rank instruction will work properly.

All seems to work ok unless I turn protection on in any of the si
pages. I then receive an error 1004 on the instruction below.

For Each cell In Columns(6).SpecialCells(3) - This is the sixth lin
of code below.

This is the first VB code I have tried to use and any and all hel
would be appreciated. I am running excel 2000 (9.0 3821 SR-1)

I realize this is confusing and I can email entire spreadsheet t
anyone that might have an idea.

Thanks in advance for the help.

Private Sub Worksheet_Calculate()
With Application
.ScreenUpdating = False
ActiveSheet.Unprotect
Dim cell As Range
For Each cell In Columns(6).SpecialCells(3)
If cell.Value = "x" Then
.EnableEvents = False
If cell.Offset(0, -1).Value < 1000 Then
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value + 1000
.EnableEvents = True
End If
Else
If cell.Value < "" And cell.Offset(0, -1).Value 1000 Then
.EnableEvents = False
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value - 1000
.EnableEvents = True
End If
.EnableEvents = True
End If
Next cell

For Each cell In Columns(9).SpecialCells(3)
If cell.Value = "x" Then
.EnableEvents = False
If cell.Offset(0, -1).Value < 1000 Then
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value + 1000
.EnableEvents = True
End If
Else
If cell.Value < "" And cell.Offset(0, -1).Value 1000 Then
.EnableEvents = False
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value - 1000
.EnableEvents = True
End If
.EnableEvents = True
End If
Next cell

For Each cell In Columns(12).SpecialCells(3)
If cell.Value = "x" Then
.EnableEvents = False
If cell.Offset(0, -1).Value < 1000 Then
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value + 1000
.EnableEvents = True
End If
Else
If cell.Value < "" And cell.Offset(0, -1).Value 1000 Then
.EnableEvents = False
cell.Offset(0, -1).Value = cell.Offset(0, -1).Value - 1000
.EnableEvents = True
End If
.EnableEvents = True
End If
Next cell
ActiveSheet.Protect
.ScreenUpdating = True
End With
End Su

--
Message posted from http://www.ExcelForum.com