1

It doesn't seem that conditional formatting is able to achieve what I'm trying to do. I'm looking for a way to make a range of cells to each auto-update its own border color based on the duration of when its data was last modified. The cell data is arbitrary, i.e. any number or text.

Border colors should indicate how long ago was the cell updated: yellow = less than 3 months ago, orange = less than 6 months ago, red = 6 months ago or more.

I'm not familiar with if Google Sheets keeps track of last modified per cell by default or offers version control information per cell.

If it comes to it, using cell Comment to keep track of last changes and timestamps would be alright, but preferably, I could just punch in numbers or text into a cell, instead of doing that and then also adding a comment to the cell.

Hope that makes sense.

Thanks!

Improve this question
5
  • Write Google Apps Script code to take notes in another sheet when cells in your sheet update. Then create conditional format based on the dates.
    – ZygD
    Oct 31, 2022 at 19:53
  • Hello! That's a good one too, combining timestamps in another sheets and conditional formatting. You'll not be able to work with the cell border color, but you can use another options (i.e. background color, font, underline, etc.)
    – Martín
    Oct 31, 2022 at 20:07
  • @ZygD Can conditional formatting color the cell borders? How would the code be able to keep track of cells? What if I added another column in my main sheet and forget to add it in the other sheet?
    – Transattic
    Nov 4, 2022 at 16:50
  • @Martín, is there another way to color cell borders?
    – Transattic
    Nov 4, 2022 at 16:52
  • Hello @Transattic, with code there is, yes. I can try some a bit later
    – Martín
    Nov 4, 2022 at 17:02

1 Answer 1

Reset to default
0

I don't think there's a way already to do that with prior changes. Maybe you could set notes with timestamps from now on, and then maybe with some menu, time trigger or onOpen fetch that data from the notes and apply the changes. What do you think of that??

Another similar way (depending on your spreadsheet) would be to record that data on analog cells from another sheet (for example a tab called Record), so in A1 from Record you'll have the timestamp of A1 from Sheet1, and so on

Just thinking outloud... if you think some of these ideas could be useful, I could try to go a litle further with the coding

Improve this answer
1
  • Hi @Martín, I feel that notes is more cumbersome of the 2 options. The mouseover for notes is slower than comments, so I'd rather not use it. I think using a second sheet to track the timestamp is a better option. As far as conditional formatting goes, I am already using the cell background color conditional formatting for a different purpose. And I prefer the keep the font color always black for easy readability. That's why I'm looking for a way to modify the cell border color to indicate the time duration of my last edit. Can it technically be coded with a script?
    – Transattic
    Nov 4, 2022 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.