Sub Error with Sheet Protection Enabled?

  • Thread starter Thread starter pcsis
  • Start date Start date
P

pcsis

Hi All,

I am at a loss, I get an error in my sub when I try and run it with the
sheet protection enabled.
I've made sure the range of cells are not locked but it has no affect.
I'm sure that it has something to do with the fact that it is
Workbook_SheetSelectionChange type sub but I don't know how to rectify.
Any assistance would be welcome.

'This sub changes only the active cell with in the range green and bolds the
text in the cell beside the active green cell

Public Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Excel.Range)

If Intersect(Target, Range("D7:D9,F7:F9")) Is Nothing Then Exit Sub 'If
the selected cell is not in the range exit the sub
Static DataField As Range

If Not DataField Is Nothing Then

DataField.Interior.ColorIndex = xlNone

End If 'changes the offset cell text to bold
Target.Interior.ColorIndex = 4
With Target.Interior.ColorIndex = 4
ActiveCell.Offset(0, 1).Font.Bold = True
ActiveCell.Offset(0, 1).Font.ColorIndex = 3
End With
If Range("D7,F7").Interior.ColorIndex = xlNone Then
Range("E7,G7").Font.Bold = False
End If
If Range("D8,F8").Interior.ColorIndex = xlNone Then
Range("E8,G8").Font.Bold = False
End If
If Range("D9,F9").Interior.ColorIndex = xlNone Then
Range("E9,G9").Font.Bold = False
End If

Set DataField = Target

End Sub


Thanks in advance.
Pete
 
Public Sub Workbook_SheetSelectionChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)

If Intersect(Target, Range("D7:D9,F7:F9")) Is Nothing Then Exit Sub 'If
the selected cell is not in the range exit the sub
Static DataField As Range

sh.unprotect password:="abcd"

If Not DataField Is Nothing Then

DataField.Interior.ColorIndex = xlNone

End If 'changes the offset cell text to bold
Target.Interior.ColorIndex = 4
With Target.Interior.ColorIndex = 4
ActiveCell.Offset(0, 1).Font.Bold = True
ActiveCell.Offset(0, 1).Font.ColorIndex = 3
End With
If Range("D7,F7").Interior.ColorIndex = xlNone Then
Range("E7,G7").Font.Bold = False
End If
If Range("D8,F8").Interior.ColorIndex = xlNone Then
Range("E8,G8").Font.Bold = False
End If
If Range("D9,F9").Interior.ColorIndex = xlNone Then
Range("E9,G9").Font.Bold = False
End If

Set DataField = Target

sh.protect password:="abcd"
End Sub
 
Back
Top