Excel VLookup in a macro where returned column changes every month
I hope I can explain this clearly. I have an Excel file that shows YTD months and a total that I need to create a macro for to do some formatting, and a VLookup. I know how to do that. Problem is, the column # in the VLookup will change every month as the total column will get pushed over as a new month is added. I attached a mock up of the file, if you go to the SUGAR worksheet you will see the macro button. Running it now works fine because the VLookup is =VLOOKUP(B:B,Sheet1!C:H,5,FALSE) where column # 5 is the total column from the pivot table. Next month when April is added the total column # will be 6, and so on. Not sure how to control that in the macro. Hope this makes sense.
thx experts...
BobR vlookup-question.xlsm