Change Color of Range After Data Have Been Posted

[Alex881]

Hello.

I am wondering if it's possible to have conditional formatting work to change the color of a range of data after an elapsed period of time.

For example, I want to be able to paste data into cells in Excel. Then, after 30 seconds, the cells that I just posted to will change from no coloring to a green color.

I was wondering if this sort of thing is possible.

Thanks

 

[Joe4]

You would need to add a timestamp that "captures" the exact date/time when you did the paste command, and then use that value in your conditional formatting.
VBA would almost certainly have to be involved.

 

[Alex881]

Interesting. Thanks for the reply. Is there anyone that would be willing to give me an example of what this code would look like?

 

[Joe4]

We need more details from you,
Where are you copying the data from?
Where will you be pasting it?
What range should be highlighted?

The number of rows/columns matters, as it affects how we will write the code. Even if it isn't exactly the same every time, it is important to know the constraints/limits.

 

[Alex881]

I'm copying data from a website and pasting into cells located in columns C & D of my spreadsheet. I'd just like for whatever data I post, 30 seconds after, to be highlighted green. I might be able to figure out the code with an example and edit it for myself, if need be. I think this one's a pretty fascinating example.

 

[Joe4]

You would want to use a Worksheet_Change event procedure, which is code that runs automatically when data is manually updated (or pasted).
Maybe paste the current date/time in column E of each row you are pasting the data too (if you Google "Excel VBA Worksheet_Change TimeStamp", you should find lots of examples).
Then maybe add Conditional Formatting code to the same VBA code that turns it green once the value in column E is more than 30 seconds in the past.
Or maybe you can play around with Timers or something, and make it turn green in the same code.

 

[Alex881]

Thanks for the ideas. I hope I can figure it out. If not, I may write another post.

 

[Joe4]

See how far you get, and if you run into issues, post back here.

 

[Alex881]

Honestly, I've tried to figure this out but it's confusing me. So far, I've been able to manually input data into column 'C' of my spreadsheet and have column 'F' put a timestamp in its respective cell. However, I can't get it colored green nor can I overcome the fact that when I paste data (into columns C-E), the timestamp isn't created because it won't recognize pasted data (if that makes sense).

Here's my current VBA code (note that I actually paste data into columns 3-5 most of the time; however, sometimes I manually input data into column 3 - so whatever code would have to work both ways, which I'm sure it can):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCellColumn As Integer
Dim xTimeColumn As Integer
Dim xRow, xCol As Integer
Dim xDPRg, xRg As Range
xCellColumn = 3 'PASTE COLUMN
xTimeColumn = 6 'TIMESTAMP COLUMN
xRow = Target.Row
xCol = Target.Column
If Target.Text <> "" Then
If xCol = xCellColumn Then
Cells(xRow, xTimeColumn) = Now()
Else
On Error Resume Next
Set xDPRg = Target.Dependents
For Each xRg In xDPRg
If xRg.Column = xCellColumn Then
Cells(xRg.Row, xTimeColumn) = Now()
End If
Next
End If
End If
End Sub

 

[Joe4]

So, this is the Worksheet_Change code I would use:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check to see if any cells updated in column C
    Set rng = Intersect(Target, Columns("C:C"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells in column C that were just updated
    For Each cell In rng
        Application.EnableEvents = False
'       Put timestamp in column F
        cell.Offset(0, 3) = Now()
        Application.EnableEvents = True
    Next cell

End Sub

and this is the Conditional Formatting I would use (first selecting column F, and applying the Formula to that):

=AND(F1>0,(NOW()-F1)>=(1/(1440*2)))

and choose your green color.

The only caveat it is the Conditional Formatting only changes when a new calculation happens, or a different cell is selected on the sheet after 30 seconds.
(So if you were to add a new entry, and do nothing for 30 seconds, the color wouldn't change until you do something, anything on the sheet, so that a new calculation is triggered).