call sub(arg1 as Range): Error 424: object required

[Hans Troost]

dear all

My question is only technical: how to call a sub expecting a Range argument, using a range.
For functionality the "poor man's solution" shown below works, so I can continue, but don't like it.

I have to call mysub (arg1 as Range) for a column in a table.

the "poor man's solution" that perfectly works is:

mysub(ActiveSheet.Range("table name[column name]")) ' as in ActiveSheet.Range("Uitgiftelijst[VB.nu]"))

But I don't find it very elegant, because I copy/pasted the table from another sheet.
So I have a named range "table name"
- and defined it as ListObject
- and did a lot of things (formatting etc.) with it (except calling a this sub)
- e.g. mytable.ListColumns("column name").DataBodyRange.Font.name = "font name" etc. etc.


dim myTable as Listobject
set myTable = activesheet.listobjects("table name")

But calling the mysub(arg1 as Range) fails all the time with error 424: Object required
What I tried is
mysub(myTable.ListColums("column name").DataBodyRange
mysub(myTable.ListColums("column name").Range
mysub(myTable.ListColums("column name") resulting in Compile Error, Data type mismatch


Question:
how to call a mysub (arg1 as Range) with the elegant use of my already defined table references?

Example table and code : see the code for what i tired to achieve. Kept the working version and commented out the error triggering lines:

Category	Jan	Feb	Mar	Apr	May	Jun	Total Column
Alpha	53	33	27	49	50	39	251
Beta	42	78	41	20	74	62	317
Charlie	39	21	64	22	27	41	214
Deltal	59	33	31	61	45	37	266
Echo	29	31	29	55	44	69	257
Foxtrot	43	30	35	44	63	54	269
Golf	22	43	35	53	65	72	290
Hotel	71	42	41	79	61	31	325

Option Explicit

Sub CondForm()

Dim myTbl As ListObject

Set myTbl = ActiveSheet.ListObjects("myTable")

With myTbl.ListColumns("Category").DataBodyRange
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$H2>260"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = vbRed
        .TintAndShade = 0.875
    End With
    .FormatConditions(1).StopIfTrue = False
End With

RemoveCF (ActiveSheet.Range("myTable[Category]")) ' Works! but prefer to not use Activesheet.<something> but functionality of my defined table: myTbl.ListColumns("Category")

'RemoveCF (myTbl.ListColumns("Category").DataBodyRange) ' Runtime error 424: Object Required. Why: this is a Range isn't it ?
'RemoveCF (myTbl.ListColumns("Category").Range) ' Runtime error 424: Object Required. Why: this too is a Range isn't it ?
'RemoveCF (myTbl.ListColumns("Category")) 'Compile error, type mismatch: understandable, is not a Range
'RemoveCF (myTbl.ListColumns("Category").Range.Select) ' Runtime error 424: Object Required
'what else?

End Sub

Sub RemoveCF(ByRef mySel As Range)

    Dim myCell As Range
    
    For Each myCell In mySel
        myCell.Interior.Color = myCell.DisplayFormat.Interior.Color
    Next myCell
    
    mySel.FormatConditions.Delete

End Sub

Any help appreciated

Kind regards, Hans Troost

 

[Fluff]

How about

RemoveCF myTbl.ListColumns("Category").DataBodyRange

 

[Hans Troost]

How about

RemoveCF myTbl.ListColumns("Category").DataBodyRange

Thank Fluff, this works.

And of course, I should have tried and I don't know why: kind of a road block in my brain?

But besides that: what is the reason this works: is this that - with the parentheses - it is calling a function in stead of a sub what was the intention?

Anyway: I'm very happy with your answer. It leaded me out of my tunnel vision when looking for solutions.

Best regards, Hans

 

[Fluff]

You would need the brackets if you "called" the sub like

Call RemoveCF(myTbl.ListColumns("Category").DataBodyRange)

but not the way you did it.

 

[Hans Troost]

Thanks again Fluff, I understand it now and I will never forget: my memory is better than my “blocked” brain. Hans

 

[Fluff]

You're welcome & thanks for the feedback.