VBA - Only working on first cell in range

[Lee Rabbit]

Hi All,

Spot of trouble here. I am trying to prevent certain cells from being modified so when they are selected, an error message displays and the user is offset by one cell down.

The code below sort of works, but only for the first cell in the declared range. I require it to work on all cells in the range.

I am probably missing something so silly but I need your guidance as to where I am going wrong.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
    
    If Target.Cells = aCell Then
        
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Cells(Target.Row, Target.Column).Offset(1, 0).Select
            
    End If
           
End Sub

Thank you in advance for your help.

Regards,
Lee

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
    
    If Not Intersect(Target, aCell) Is Nothing Then
        
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Target.Offset(1, 0).Select
            
    End If
           
End Sub

 

[Lee Rabbit]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range
    Set aCell = ActiveSheet.Range("B2,C2,E2,F2,E16,F16,H2,I2,H6,I6,H16,I16,K2,K5,K7,L7,K16,L16,L2")
   
    If Not Intersect(Target, aCell) Is Nothing Then
       
        MsgBox " THIS CELL CANNOT BE MODIFIED " _
        , vbCritical, "ERROR MESSAGE"
            Target.Offset(1, 0).Select
           
    End If
          
End Sub

Fluff, we must stop meeting like this. But once again, thank you for your help, works perfect!

Cheers,
Lee

 

[Fluff]

You're welcome & thanks for the feedback.