Im working on a spreadsheet with about 100-150 worksheets in it. On each sheet there are two dates(one pre-determined and one the current date) that I want to compare to determine whether or not they are <4 years nine months apart, >4 years 9 months apart or >5 years apart. Each state will make the tab a certain color, green, yellow and red respectively.
Im using the worksheet_change event right now to update the color after Ive entered a date using this code:
If ActiveSheet.Cells(5, 14) - ActiveSheet.Cells(5, 1) >= 1828 Then
ActiveSheet.Tab.ColorIndex = 3
ElseIf ActiveSheet.Cells(5, 14) - ActiveSheet.Cells(5, 1) >= 1736 Then
ActiveSheet.Tab.ColorIndex = 6
Else
ActiveSheet.Tab.ColorIndex = 4
End If
My problem now is when I open the workbook, the colors wont update without a change to each sheet. I want to make it so that when I open the workbook, the colors update themselves. I can do this right now using the workbook_activate event and this code:
If Sheet24.Cells(5, 14) - Sheet24.Cells(5, 1) >= 1828 Then
Sheet24.Tab.ColorIndex = 3
ElseIf Sheet24.Cells(5, 14) - Sheet24.Cells(5, 1) >= 1736 Then
Sheet24.Tab.ColorIndex = 6
Else
Sheet24.Tab.ColorIndex = 4
End If
Doing this however requires I input one copy of this code for each sheet(thats a lot). Im looking for a way to do this with much less code, maybe referencing the cells a different way(ie ActiveWorkbook.something.something?
Is it possible or do you guys have any other suggestions for a more efficient way to do it?
Thanks
Jon
Bookmarks