I've been given another small task, which I thought I could get working by using Worksheet_Change(ByVal Target As Range), but I can't get the sub to trigger.
Cell A1 is already populated with a customer reference.
When this file (physical file on desk, not computer file )has been completed, we highlight the reference in yellow.
We also then have to put the date into column D (same row) (day of completion as such).
I can't get the sub to trigger when the cells colour is changed.
Is there another way of doing this??
NOTE: This needs to work with Excel 2003. I'm not sure if this can be achieved by using conditional formatting??
Thanks
This is a little snippet of code that I have already
Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Cells.Count > 1 Then
Exit Sub
End If
If Target.Column = 1 Then
Application.EnableEvents = False
If Target.Interior.ColorIndex > 0 Then
Target.Offset(0, 4).Value = Date
End If
Application.EnableEvents = True
End If
End Sub
EDIT:
I've actually just come up with this.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Watch column equal to 1 (Column A), if any cells are double clicked then continue with procedure
If Target.Column = 1 Then
Application.EnableEvents = False
'If anything is currently inside the double clicked cell then continue with procedure
If Target.Value > 0 Then
'Change the colour of the cell to yellow
Target.Interior.Color = 65535
'Enter "Current Date" into Column B
Target.Offset(0, 1).Value = Date
'Correctly format the date
Target.Offset(0, 1).NumberFormat = "dd.mm.yyyy"
End If
Application.EnableEvents = True
End If
End Sub
This highlights the cell, and inserts the date when I double click on the reference... Which is good, but I would have to do this for each reference (sometimes I have 10 - 20 files that I have to mark as completed). I still think the highlighting method would be better, but can't figure out how to do it.