Give this
workbook event code a try (it will automatically make all date years be in the 2000's when they are entered into a cell in Column F or G on the indicated sheets)...
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If UBound(Filter(Array("repo", "retail", "MBs abs prov", "bill", "ba"), Sh.Name, True, vbTextCompare)) > -1 Then
If Target.Column = 6 Or Target.Column = 7 Then
If Year(Target.Value) < 2000 Then Target.Value = DateAdd("yyyy", 100, Target.Value)
End If
End If
End Sub
HOW TO INSTALL Event Code
------------------------------------
If you are new to workbook event code procedures, they are easy to install. Go into the VBA editor (ALT+F11) and look at the small windows to the left... find the one titled "Project-VBA Project" and double-click the entry in it named "ThisWorkbook"... that will open the workbook's code window. This will open up the code window for the workbook. Copy/Paste the above event code into that code window. That's it... the code will now operate automatically whenever a date is entered in Column F or G on any of the indicated worksheets. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.