Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cel As Range
Dim rngC30C187 As Range
Dim counter As Long
Dim C6Check As Boolean 'Default is FALSE at declaration
Application.EnableEvents = False 'in order that no one can change selection while code is running
Set rngC30C187 = Sheets("Data").Range("C30:C187")
Select Case Sheets("Data").Range("C6").Value
Case "CP18", "CP18 TM", "M21Z", "M25Z"
C6Check = True
End Select
If C6Check Then
rngC30C187.Interior.ColorIndex = xlColorIndexNone
Range("H30:H187").Value = ""
For Each Cel In rngC30C187
If (Cel > 2.25 Or Cel < -2.25) Then
Cel.Interior.ColorIndex = 44
Cel.Offset(0, 5) = "Error " '& counter 'Offsets to column H
End If
Next Cel
End If
Application.EnableEvents = True
End Sub
Personally, I prefer to use an Excel Control Toolbox button to run subs like yours. Or sometimes I format a cell to look like a button then use
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target address Not "A2" Then 'use your cell address here
Cancel = True
Exit Sub
End If
'regular code goes here