I need a timestamp into cell p when cell a is colored in yellow

[michellin]

Hi there,

I need a macro to put the today's date into column P (from P2 to the ends), only if column A is colored in yellow#6(A2 to the end). that date will be not updated if i move of day, i need it to be original when the cell will be colored, and never change.

I'l try conditional formating, not good, i'l try function, not good.

So i'm ask here to get a macro or another idea to get it work please.

Michellin

 

[Joe4]

Conditional Formatting can only update formatting, not values. So you cannot use Conditional Formatting for time stamps.
How exactly is column A's color being changed? Is it being down manually, or by Conditional Formatting?

If it is to happen when someone manually changes the color of the cell, it could be kind of tricky, as changing the format of a cell does not trigger a calculation, which we would typically use to detect changes in the worksheet (i.e. Worksheet_Change event procedure code).

We could use Worksheet_SelectionChange event procedure code, which runs every time a new cell is selected, but if you were to change the color of a cell, and not move off of that cell before saving/closing, the code would never run.

We could also Workbook_BeforeClose event procedure code, which would run the code to check column before the file is closed.

Or perhaps you want to consider a different approach, such as instead of having the users manually change the color of the cells, have them select the cell that they want to change yellow, and click a button (or keyboard shortcut) that would run code to change the cell's color and update the time stamp.

There may be other options, these are just the ones that I can think of, off the top of my head.

 

[michellin]

Thanks Joe4

I give you more stuff to know.

I got a sheet of thing i need to be done, like task.

When i finish it, i put the a cell in yellow, job done.

After my day i run a macro, to take all those yellow row and copy it to another sheet.

I'v try 2 approch, put the time stamp before moving the line. Never get it done right.
Or correct the macro to cut and paste only column from A to O and got a function when line is paste put a time stamp in P column. I got the function running, but i'm pasting all the line so the function disapear.

That my cheap macro i'v build with stuff i found everywhere.

Sub Task()

a = Worksheets("task").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a
If Worksheets("task").Cells(i, 1).Interior.Color = vbYellow Then
Worksheets("done").Rows(i).Cut
Worksheets("task").Activate
b = Worksheets("done").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("done").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("done").Activate
End If
Next
Dim Ligne As Long
For Ligne = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(Ligne, "A") = "" Then Rows(Ligne).Delete
Next Ligne
End Sub

So if your able to correct my macro to cut and paste only the row from A to O, i will manage to refind the function to put a date when paste it.

Thanks a lot for you time.
Michellin

 

[michellin]

Conditional Formatting can only update formatting, not values. So you cannot use Conditional Formatting for time stamps.
How exactly is column A's color being changed? Is it being down manually, or by Conditional Formatting?

If it is to happen when someone manually changes the color of the cell, it could be kind of tricky, as changing the format of a cell does not trigger a calculation, which we would typically use to detect changes in the worksheet (i.e. Worksheet_Change event procedure code).

We could use Worksheet_SelectionChange event procedure code, which runs every time a new cell is selected, but if you were to change the color of a cell, and not move off of that cell before saving/closing, the code would never run.

We could also Workbook_BeforeClose event procedure code, which would run the code to check column before the file is closed.

Or perhaps you want to consider a different approach, such as instead of having the users manually change the color of the cells, have them select the cell that they want to change yellow, and click a button (or keyboard shortcut) that would run code to change the cell's color and update the time stamp.

There may be other options, these are just the ones that I can think of, off the top of my head.

Thanks Joe4

I give you more stuff to know.

I got a sheet of thing i need to be done, like task.

When i finish it, i put the a cell in yellow, job done.

After my day i run a macro, to take all those yellow row and copy it to another sheet.

I'v try 2 approch, put the time stamp before moving the line. Never get it done right.
Or correct the macro to cut and paste only column from A to O and got a function when line is paste put a time stamp in P column. I got the function running, but i'm pasting all the line so the function disapear.

That my cheap macro i'v build with stuff i found everywhere.

Sub Task()

a = Worksheets("task").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a
If Worksheets("task").Cells(i, 1).Interior.Color = vbYellow Then
Worksheets("done").Rows(i).Cut
Worksheets("task").Activate
b = Worksheets("done").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("done").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("done").Activate
End If
Next
Dim Ligne As Long
For Ligne = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(Ligne, "A") = "" Then Rows(Ligne).Delete
Next Ligne
End Sub

So if your able to correct my macro to cut and paste only the row from A to O, i will manage to refind the function to put a date when paste it.

Thanks a lot for you time.
Michellin

 

[Joe4]

So, how about instead of manually highlighted the code yellow, you run a macro that puts the timestamp in column P of the active row (if column P has not already been populated)?
You can even attach the VBA code to a button or keyboard shortcut to make it quick and easy to use.

Then you can use filters to get the data that you want (it is easier to filter rows on data instead of formatting). You can even run those filters by VBA, if you like (or use some other VBA method to quickly and easily copy the data).

 

[michellin]

So, how about instead of manually highlighted the code yellow, you run a macro that puts the timestamp in column P of the active row (if column P has not already been populated)?
You can even attach the VBA code to a button or keyboard shortcut to make it quick and easy to use.

Then you can use filters to get the data that you want (it is easier to filter rows on data instead of formatting). You can even run those filters by VBA, if you like (or use some other VBA method to quickly and easily copy the data).

Hummmmmm love the idea, i still need to color it, but like you said, i can use a shorcut in my ribbon, to color the A cell and put time stamp in P, then after my day, just run my macro i'v already have to move all those data of the way.

That's why i love this forum, you don't find only stuff, you get brainstorm with expert I was to close of my problem to think.

Thanks a lot Joe4, you just make my day, i woul go create that simple macro. Thanks

 

[Joe4]

You are welcome.

The code for that little macro might look something like this:

Sub MyDateStampMacro()

    Dim r As Long
    
'   Get current row
    r = ActiveCell.Row
    
'   Check to see if column P is blank
    If Cells(r, "P") = "" Then
'       Put date in column P
        Cells(r, "P") = Date
'       Make column A yellow
        Cells(r, "A").Interior.Color = vbYellow
    End If
        
End Sub

 

[michellin]

You are welcome.

The code for that little macro might look something like this:

Sub MyDateStampMacro()

    Dim r As Long
   
'   Get current row
    r = ActiveCell.Row
   
'   Check to see if column P is blank
    If Cells(r, "P") = "" Then
'       Put date in column P
        Cells(r, "P") = Date
'       Make column A yellow
        Cells(r, "A").Interior.Color = vbYellow
    End If
       
End Sub

That work great, thanks for the idea and the macro. You rock

 

[Joe4]

You are welcome.
Glad it worked for you!