I need a timestamp into cell in columns X11 when cell in column X1 is colored manually

[Dashivas123]

Hi all,

I need a macro to put the today's date into column X1, X2, X3, X4 , which are for example the 10th, 11th, 12th column to the right of X1,X2,X3 etc. ((f.e. columns X11, X12, X13, X14 etc)) . if the cell in the original columns are changed color manually. The timestamp automatically needs to be put in X11,X12,X13 etc. if the cells in X1,X2,X3 etc. changed color manually (so in excel with manually fill color button).

I already found with VBA how to do this if the cells changes values (like in character/data changes), but this does NOT work when the cell changes color (only if new data is entered). I need the timestamp if the color is changed. See the script I already found below.

I searched for a long time on internet and threads but without succes so far, thanks in advance! Please I need your help,

Dash


Code for timestamps in 18th column to the right of changed column cell when data in cell is changed, but still not for manual color changes:

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("C:C, D:D, E:E, H:H, I:I, J:J, M:M, N:N, O:O"), Target)
xOffsetColumn = 18
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Also posted here I need a timestamp into cell in columns X11 when cell in column X1 is colored manually

 

[Joe4]

Welcome to the Board!

I already found with VBA how to do this if the cells changes values (like in character/data changes), but this does NOT work when the cell changes color (only if new data is entered). I need the timestamp if the color is changed

Unfortunately, I do not know any way of doing that, which is probably why you are having such a hard time finding anything on the internet.
There is event procedure code (VBA code that runs automatically upon some event happening) for things like manually changing the value of a cell, selecting a cell, and the sheet being re-calculated, but there is not anything for the format being changed. And changing the format does not force a re-calculation.

Not saying it is impossible, but I cannot think of any good way to do it the way you want. You may need to take a different approach, such as locking the whole sheet, and forcing them to make those sort of changes through VBA code (i.e. by clicking a button or manually running a macro). Then, you could handle all of that (changing the color and adding the timestamp) through your code, which re-locks the sheet again after running.