VBA Code not working from Button, but does when run within VBA editor

[grabrail]

Bit of a strange one.

I have a workbook, that has multiple worksheets that are identical. These are used to collect inspection data.

I have some vba code that is used to ensure all required cells have been filled in, and a pop up message if they have missed any

the code is as follows:

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect Password:="******"

Dim i As Long

For i = 1 To 20

If Worksheets("DPU Report " & i).Range("C6").Value <> "" Then

Worksheets("DPU Report " & i).Activate
    
    Dim data As Range
    Dim cell As Range
    Dim Counter As Integer
    
    Counter = 0
    
    Set currentsheet = ActiveWorkbook.Sheets("DPU Report " & i)
    Set data = currentsheet.Range("H2, C5:C7, C9, H5:H7, H9:H16") ', E19:E33, J19:J34")

    For Each cell In data
        If cell.Value = "" Then
            'cell.Interior.Color = 7
            Counter = Counter + 1
        Else
            cell.Interior.Color = RGB(255, 255, 255)
        End If
    
    Next
    
    If Counter > 0 Then
        
        MsgBox ("Please complete boxes highlighted in Pink, Then Validate Form Again")
        'valcheck.Value = False
        ActiveSheet.Protect Password:="******"
        Exit Sub
    Else
    MsgBox ("No Issues Found, OK to Save Form")
    End If
    
'    'Spell Check
'    Range("B38:CB50").CheckSpelling
    
    End If
    Next i
End Sub

If I run this using the play button in the VBA editor, it works perfectly, I have populated sheet 1 fully and sheet 2 partially to test. It pops up the message "No Issues" on sheet 1, and sheet 2 the message "Please complete boxes highlighted in Pink, Then Validate Form Again" Great

However, this code is assigned to a button, and when I try to run the code using the button, it does sheet 1 perfecty, but then errors on sheet 2

With the line

cell.Interior.Color = RGB(255, 255, 255)

Highlighted as the issue.

How come this is working using the play button, but not working when using the button to run it?

 

[shinigamilight]

Can you paste the code in a module and then try to use the CALL keyword to call the macro.

 

[davesexcel]

I'm just grasping, it looks like your code has not un-proteced the sheet in the loop before coloring the cell

 

[grabrail]

I'm just grasping, it looks like your code has not un-proteced the sheet in the loop before coloring the cell

You know what I think you're right, the sub unprotects it, then it is protected again in the loop, but it doesnt unprotect it again within the loop, schoolboy error, DOH! thank you