I am looking to automate changing a field's fill color (or text color) when I change the value of that field. Based on my understanding of FM, I don't think conditional formatting would work, because once the field has been changed, I want to permanently lock in the fill color for that specific record.
Could a simple script trigger attached to the field do this? I'm having trouble wrapping my head around how to reference and compare the original and new values via script or calculation. Thank you in advance!
Additional info on this solution:
I recently inherited the responsibility of updating our department's weekly, "sheet." It was a simple excel sheet that I converted into a much more useful FM solution.
The "sheet" lists all the current projects going at a given time. Its about 30 projects, and 30 fields, consisting of dates, budgets, names of managers etc. Each week we update it and print a new copy for everyone.
The legacy way of doing this was to save a new copy of the xlsx file. My solution was to write a script that sets each field as a variable, creates a new record and pastes the value, effectively creating a duplicate or all 30 records. I created a "master date version" field, to distinguish the current and past versions. All versions of "the sheet" exist in a single table. A practical example is, we might change:
Manager: person 1
Date: last week
Manager: person 2
Date: this week
Project 4 changed managers, so this field should change color. In the past, the dept head would manually change fill color to grey for any fields that changed, to draw attention to the changes. The fill color is not so much important, I'm just looking for a way to indicate, on a printed copy, that a change has occurred. Could be changed to red text, bold text etc.