Determine which number in a table a column is

[mst3k4L]

I'm not used to programming with tables, so my apologies if this is really simple.

So I originally needed to highlight certain cells in a row, but not the whole row. The code looked something like this.

Sub QC()

    Dim row As Range

    For Each row In [CAPEX].Rows          
                row.Columns(row.ListObject.ListColumns("Program name").Index).Interior.Color = 49407
'               * a whole lot more lines here which i deleted for this post*
                row.Columns(row.ListObject.ListColumns("Total Forecast").Index).Interior.Color = 49407
    Next
End Sub

But I soon realized that I could just do this instead

Sub QC()

    Dim row As Range

    For Each row In [CAPEX].Rows          
                        For i = 2 To 12 Step 1
                row.Columns(row.ListObject.ListColumns(i).Index).Interior.Color = 49407
            Next i
    Next
End Sub

A thought occured to be though. There's a chance that someone might add an extra column between column "Program name" and column "Total Forecast", and I would still like all the cells to be highlighted. I was thinking of something like this, but I have no idea how to do that.

Sub QC()

    Dim Program_name_column As Int
    Dim Total_Forecast_column As Int
    Dim row As Range

    For Each row In [CAPEX].Rows   

            Program_name_column = ?
            Total_Forecast_column = ?   
    
            For i = Program_name_column To Total_Forecast_column Step 1
                row.Columns(row.ListObject.ListColumns(i).Index).Interior.Color = 49407
            Next i
    Next
End Sub

So how would I go about setting those two variables dynamically in the program? Thank in advance.

 

[Momentman]

If you just need the column, am assuming for example that its on row 1, I would do something like

Range("A1:XFD1").Find("program name").Column

 

[mst3k4L]

If you just need the column, am assuming for example that its on row 1, I would do something like

Range("A1:XFD1").Find("program name").Column

That worked. Thanks! Incase the table no longer starts on row 1, how would you just say to start with the first row in the Table?

 

[stefankemp]

Why do you loop through the rows? You are not testing if a row meets some criteria so why not change all the rows at once?

dim tbl as listobject
dim cols as long

set tbl =[capex].cells(1).listobject

with tbl
   cols = .listcolumns("Total Forecast").index - .listcolumns("Program name").index + 1
   .listcolumns("Program name").databodyrange(1,1).resize(.listrows.count,cols).interior.color = 49407
end with

 

[pgc01]

Hi

Assuming the table in the active sheet, try also:

Sub Test()
Dim lstobj As ListObject
Dim r As Range

Set lstobj = ActiveSheet.ListObjects("Capex")

Set r = Range(lstobj.ListColumns("Program Name").Range, lstobj.ListColumns("Total Forecast").Range)

r.Interior.Color = 49407

End Sub