Syntax for range (column) in vbs

[PW1202]

My code so far is:

Option Explicit

Private Sub Worksheet_Calculate()

Dim Rng1            As Range
Dim Value           As Double
Dim Prompt          As String
Dim Title           As String

    'Put the range you want to test here
    Set Rng1 = Range("T14")
    
    'Put the target value here
    Value = "1"

    'Put the message (prompt) of the message box (pop up) here
    Prompt = "Sub group must be part of the Group"

    'Put the title of the message box (pop up) here
    Title = "Group/ Sub Group mismatch"

    If Rng1.Value = Value Then
            MsgBox Prompt, vbInformation, Title
    End If

End Sub

and it works well when the range is "T14", but I want to module to check any (all) cells in column T. I have tried "$T" and other variations, but they all fail. Need the syntax for column T as the range.


Would appreciate any help.

Peter.

 

[Special-K99]

Try (untested)

Set Rng1 =Range("T:T")

 

[Fluff]

Hi & welcome to the board.
How about

Private Sub Worksheet_Calculate()

Dim Cl              As Range
Dim Value           As Double
Dim Prompt          As String
Dim Title           As String

    
    'Put the target value here
    Value = "1"

    'Put the message (prompt) of the message box (pop up) here
    Prompt = "Sub group must be part of the Group"

    'Put the title of the message box (pop up) here
    Title = "Group/ Sub Group mismatch"

    For Each Cl In Range("T1", Range("T" & Rows.Count).End(xlUp))
      If Cl.Value = Value Then
         MsgBox Prompt, vbInformation, Title
         Exit For
      End If
   Next Cl

End Sub