Unable to highlight selected cells on a protected worksheet

[aryang]

I have a billing excel sheet in which I use the following code to highlight any cell the user has selected. But this code doesnt work on any of my protected sheets. If I create a new sheet, it works on it.
How do I modify this VBA code to get it working on all the protected sheets.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static xLastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 6
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following, change "abc" to your sheet's password, if it doesn't have a password then just remove this "abc" part.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
  Static xLastRng As Range
  On Error Resume Next
  ActiveSheet.Unprotect "abc"
  Target.Interior.ColorIndex = 6
  xLastRng.Interior.ColorIndex = xlColorIndexNone
  Set xLastRng = Target
  ActiveSheet.Protect "abc"
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[aryang]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try the following, change "abc" to your sheet's password, if it doesn't have a password then just remove this "abc" part.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
  Static xLastRng As Range
  On Error Resume Next
  ActiveSheet.Unprotect "abc"
  Target.Interior.ColorIndex = 6
  xLastRng.Interior.ColorIndex = xlColorIndexNone
  Set xLastRng = Target
  ActiveSheet.Protect "abc"
End Sub

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thanks it works like a charm.