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.
But I soon realized that I could just do this instead
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.
So how would I go about setting those two variables dynamically in the program? Thank in advance.
So I originally needed to highlight certain cells in a row, but not the whole row. The code looked something like this.
Code:
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
Code:
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.
Code:
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.