I have 13 worksheets with 12 of them for each month. There is a cell in each of the 12 sheets with the name of the month.
I've gotten a VBA script to test if the current month matches the cell and to change the tab color accordingly, but it requires a calculation in the sheet to update.
Private Sub Worksheet_Change(ByVal Target As Range)
If Format(Date, "MMMM") = Range("E3").Value Then
Me.Tab.ColorIndex = 10
Else
Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
If I stop updating last month's sheet and move onto the next sheet, both tabs would be highlighted because the previous sheet was not updated. I just want the current month worksheet to be highlighted.
Is there a way to recalculate all sheets on workbook open and tab change?