Excel 2003, worksheet_change to insert current date when a cell is colored yellow

[ashleyuk1984]

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.

[david000]

I don't think you can fire an event just from formatting, but you can make a small Form that will color your cells and fire something at the same time. If you make a small UserForm you can call it from some shortcut keys and show it Modal, then you can select how ever many files you want to mark yellow and in this example it will put the date in the next column over. This only fires if you have a cell or cells selected in column A, otherwise you get a warning.



Sub ShowModal()
UserForm1.Show 0
End Sub






Private Sub CommandButton1_Click()
Dim cel As Range
For Each cel In Selection
If Not Intersect(cel, Columns(1)) Is Nothing Then
With cel
.Interior.Color = vbYellow
.Offset(, 1) = Date
.NumberFormat = "ddd.mmm.yyyy"
End With
Else
MsgBox "You can only select cells in column A"
End If
Next cel
End Sub

[ashleyuk1984]

Ohhhhh, I like that idea.
I've just given that a go and it's a very good solution for when I have more than 10 to check off.

Thanks David !!

[SamT]

If Target.Interior.ColorIndex <> xlColorIndexNone Then