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

Dashivas123

New Member
Joined
May 15, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,055
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top