Formula for changing cell color when value changes in another cell

Amhammer02

We would like to change the cell color of the successor if it changes due to a predecessor changing. Conditional formatting doesn't work in this instance and "highlight changes" changes every cell in the sheet that has changed. We just want to see what successors change if we change a predecessor.

I Googled a bunch of things but it kept taking me back to conditional formatting or hightlight changes. I am wondering if there is any other option (we can set up a workflow to notify the person when changes occur but would like something to show up on the sheet, if possible). I am not sure if there is some formula for this being that the successor column already has a formula in it (=JOIN(SUCCESSORS([Task Name]@row), ", ")?

For example in the screen shot below, if the 449FS+14d changes, we want to highlight the successor cell(s) that also change because of that 449FS+14d update.

Thank you in advance!

Genevieve P.

Hi @Amhammer02

You are correct that Highlight Changes will look for changes across all your cells, so that doesn't sound like a good solution here.

There currently isn't a way to use a formula to check the cell history of a specific cell (please add your vote to this related Product Idea: Enhancement Request: access values stored in cell history)

I would perhaps use a Record a Date workflow that records a date in a hidden helper column when you make a change specifically in the Successors column. Then you could use Conditional Formatting to reference the Date in that column (if it's the last X number of days) and change the Successor cell colour based on the hidden date column. Would that work for you?

Cheers,

Genevieve

Genevieve P.

Hi @Amhammer02

You are correct that Highlight Changes will look for changes across all your cells, so that doesn't sound like a good solution here.

There currently isn't a way to use a formula to check the cell history of a specific cell (please add your vote to this related Product Idea: Enhancement Request: access values stored in cell history)

I would perhaps use a Record a Date workflow that records a date in a hidden helper column when you make a change specifically in the Successors column. Then you could use Conditional Formatting to reference the Date in that column (if it's the last X number of days) and change the Successor cell colour based on the hidden date column. Would that work for you?

Cheers,

Genevieve

Amhammer02

@Genevieve P. - Thank you so much! I tested this a couple different ways and I think it is working like I want it to. Appreciate the help!