Need help making a simple modification to my code.

[pglufkin]

I have the following code which merely defines a range based on it finding a special character in a cell of text relative to where my beginning cursor is. It does this for another procedure. It works fine:

Sub rangeset()
Dim N As Long, NN As Long
Dim M As Long, col As Long
Dim reg As Range, s As String
Application.Calculation = xlCalculationManual
col = ActiveCell.Column
N = Cells(Rows.Count, col).End(xlUp).Row
M = ActiveCell.Row
For NN = M To N
s = Cells(NN, col).Value
If Asc(Right(s, 1)) = 63 Then
Set reg = Range(Cells(M, Selection.Column), Cells(NN, Selection.Column))
Exit For
End If
Next NN
Call Test_ConcatAndColorParts
End Sub

Problem is, I am trying to call this procedure while my cursor is on another worksheet and the code to do that I have come up with so far is:

Sub rangeset()
Dim N As Long, NN As Long
Dim M As Long, col As Long
Dim reg As Range, s As String
Dim shSource As Worksheet
Set shSource = ThisWorkbook.Sheets("DataSet")
col = shSource.Columns(9)
N = Cells(Rows.Count, col).End(xlUp).Row
M = shSource.Rows("3")
For NN = M To N
s = Cells(NN, col).Value
If Asc(Right(s, 1)) = 63 Then
Set reg = shSource.Range(Cells(M, 9), Cells(NN, 9))
Exit For
End If
Next NN
Call Test_ConcatAndColorParts
End Sub

It doesn't like col = shSource.Columns(9) and I am missing something here I'm not quite sure what is. Thank you for your help.

 

[shg]

col = 9

 

[pglufkin]

thank you

 

[shg]

You're welcome.