conditional formatting VBA errors with password

[andrewb90]

I have a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> that is used to highlight cells in my worksheet as I select them, and has been running without any issues, until I have password protected the sheet when I run other macros.
Below is the code that is used to protect my sheet:

[/COLOR]    ActiveSheet.Protect "GOKU"    ActiveSheet.EnableSelection = xlUnlockedCells
    ActiveSheet.EnableSelection = xlLockedCells
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _

        AllowFormattingRows:=True, AllowSorting:=True[COLOR=#333333]

Now, below is the code that is starting to have issues.

[/COLOR]Option ExplicitConst MyAreas = "G13:M23,G24:N30,G31:M33,G34:N36,G37:M38,G40:N55,G41:M72,G74:M81,G83:M97,G99:M109,G111:M116,G119:I123,G125:N128,G129:M129,G130:N133,G134:M134,G143:M157,G158:N165,G166:M166,G213:M223,G224:N230,G231:M233,G234:N236,G237:M238,G240:N255,G241:M272,G274:M281,G283:M297,G299:M309,G311:M316,G319:I323,G325:N328,G329:M329,G330:N333,G334:M334,G343:M357,G358:N365,G366:M366,G413:M423,G424:N430,G431:M433,G434:N436,G437:M438,G440:N455,G441:M472,G474:M481,G483:M497,G499:M509,G511:M516,G519:I523,G525:N528,G529:M529,G530:N533,G534:M534,G543:M557,G558:N565,G566:M566,G613:M623,G624:N630,G631:M633,G634:N636,G637:M638,G640:N655,G641:M672,G674:M681,G683:M697,G699:M709,G711:M716,G719:I723,G725:N728,G729:M729,G730:N733,G734:M734,G743:M757,G758:N765,G766:M766,G813:M823,G824:N830,G831:M833,G834:N836,G837:M838,G840:N855,G841:M872,G874:M881,G883:M897,G899:M909,G911:M916,G919:I923,G925:N928,G929:M929,G930:N933,G934:M934,G943:M957,G958:N965,G966:M966"
Dim a, MyCol As Collection, Rng As Range, x As Range
 
' Highlighting with Conditional Formatting
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Long
  If Application.CutCopyMode Then Exit Sub
  If MyCol Is Nothing Then
    ' Setup MyCol only once first time
    Set MyCol = New Collection
    For Each a In Split(MyAreas, ",")
      MyCol.Add Range(a)
      ' Clear CF highligtings in each area for the first time
      Range(a).FormatConditions.Delete
    Next
  End If
  If Not x Is Nothing Then
    ' Clear the previous CF highlighting
    x.FormatConditions.Delete
  End If
  For Each x In MyCol
    ' Check intersection
    Set Rng = Intersect(Target, x)
    If Not Rng Is Nothing Then Exit For
  Next
  If Not x Is Nothing Then
    ' Highlight row of MyAreas via CF
    i = ActiveCell.Interior.ColorIndex
    Set x = x.Rows(Rng.Row - x.Row + 1)
    With x.FormatConditions.Add(Type:=2, Formula1:=1)
      .Interior.ColorIndex = IIf(i < 0, 6, i + 1)
      .font.Bold = True
    End With
  End If
End Sub


[COLOR=#333333]

I am hoping that I just need to adjust my sheet protection instead of the line highlighting, but I am really not sure.

Any help would be greatly appreciated.

thanks,

Andrew

 

[andrewb90]

bump