400 Error when excel cells are protected

[DemonX]

I used the following code to check the duplicate records at A2 cell and last one to refresh the A2 cell. Now, when I protected the sheet ( any other cell without A2 cell too ) I get 400 error.


Sub bTest()
'A BETTER VERSION
Dim spl As Variant, rCell As Range
Dim sBaseText As String, sToHighlight As String
Dim i As Long, j As Long, lCount As Long, pos As Long
Dim dicColors As Object

For Each rCell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
'Initialize variables
sBaseText = rCell.Value
Set dicColors = CreateObject("Scripting.Dictionary")
dicColors.CompareMode = vbTextCompare

'Split cell and loop
spl = Split(sBaseText, ";")
For i = LBound(spl) To UBound(spl)
If Len(spl(i)) > 0 Then
lCount = (Len(sBaseText) - Len(Replace(sBaseText, spl(i), ""))) / Len(spl(i))
'Check if it's a duplicate
If lCount > 1 Then
If Not dicColors.exists(spl(i)) Then
dicColors(spl(i)) = dicColors.Count + 1
sToHighlight = spl(i)
pos = 0
For j = 1 To lCount
pos = InStr(pos + 1, sBaseText, spl(i), vbTextCompare)
rCell.Characters(Start:=pos, Length:=Len(spl(i))). _
Font.ColorIndex = 2 + dicColors(spl(i))
Next j
End If
End If
End If
Next i
Next rCell
End Sub


Sub Clearcells()
'Updateby Extendoffice
Range("A2:A7").ClearContents

End Sub

Do I need to do any change to code ?

 

[mart37]

You can't change cells with VBA if cells are protected.
You need to unprotect the cells you want to change by a macro or you unprotect the sheet in the macro.

 

[DemonX]

Hey Mart37. Thanks for replying. Here my concern is, I can not protect cells not covered by VBA also. my guess was error in VBA may be ?

 

[mart37]

Yes That is possible. When the sheet is unprotected does the macro work?

 

[DemonX]

Yes it works well when nothing is protected.