ashleyuk1984
03-27-2014, 02:54 AM
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.
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.