Change Excel sheet Tab colour when date entered in a cell

[m.paterson1]

I am trying to create a macro which will change the sheet tab colour to green when the form is started and red when the form is completed.

I have a Excel book containing 40 sheets labelled 1-40 but these change from job to job
When cell J5 has a date inserted I would like the tab of that sheet only to change to Green.
When the cell J9 of the same sheet has a date entered the tab will now change to Red.

i.e
I J
5 Start Date 6/4/16 (this would be J5) (J5 symbolises that the form has started so sheet tab would change to green)
9 End Date 25/5/16 (This would be J9) (J9 symbolises that the form is now complete so sheet tab now changes from green to Red)

I am recently new to VBA but a quick learner would be grateful if any one solve this for me as I've spent hours on it.
Also First time poster / long time reader.

[Paul_Hossler]

Add this to the 'ThisWorkbook' object's Workbook_SheetChange event handler (look in the attachment)



Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Tab.Color = xlAutomatic
If IsDate(Sh.Range("J5").Value) Then
Sh.Tab.Color = vbGreen
End If
If IsDate(Sh.Range("J9").Value) Then
Sh.Tab.Color = vbRed
End If
End Sub

[m.paterson1]

Thanks that's brilliant, makes my spread sheet form ive been making look very professional and have already sent of to the bosses for approval to roll out to other staff.
If your ever in New Zealand I owe you a Speights (which would be the best beer in the world)

[Allkman]

I had other way to do this, this is more expert way! Thanks

[Aussiebear]

If your ever in New Zealand I owe you a Speights (which would be the best beer in the world)

I think you've been in the bottom of too many rucks......