Change Tab Color Of Last 4 Excel Worksheets in Excel (VBA)

Question

I create an Excel spreadsheet(s) from a Perl script that reads in multiple text files to populate the worksheets in the spreadsheet. There can be a varied number of text files, hence, a varied number of worksheets created. I want to automatically change the tab color of the LAST 4 worksheets in each spreadsheet automatically in a macro (i.e. If there are 8 worksheets created, I want to change the tab color of worksheets 5, 6, 7 and 8. If there are 9 worksheets created, I want to the change tab color of worksheets 6, 7, 8 and 9, and so on). Thank you in advance.

Answer 1

Put the following code in a sub...

---

Get number of sheets

Dim numSheets As Long
numSheets = ThisWorkbook.Sheets.Count

Loop over last 4 sheets. Use max to ensure you are accessing a valid sheet number (greater than 0)

Dim i As Long
For i = Application.Worksheetfunction.Max(1, numSheets-3) To numSheets
    ' change tab colour, use any RGB triplet
    ActiveWorkbook.Sheets(i).Tab.color = RGB(230,255,100) 
Next i