Can I use conditional formatting to highlight where a cell has been changed (e.g. from N to Y)

[Dicegirly]

Hi all,

I have an Excel spread sheet setup to display departmental compliance with certain criteria, and at the moment I have conditional formatting to show the cell as red when it contains "n" and green when it displays "y".
What I would like to do is show also when a cell has been changed from N to Y, to demonstrate that the area was not compliant before but has improved and is now compliant, and maybe make that a different colour.

I can't work out how to write a rule which shows this - is there a way?
Or some other way in which anyone could think of?
(My backup plan is to make a 3rd condition called "C", meaning wasn't compliant, but now is, but I would prefer to keep the fields binary if possible for easier reporting purposes.)
Thanks in anticipation.

 

[Joe4]

Conditional Formatting can only run on what the value in the cell currently is. It has no recollection of what it was previously.
You would probably need some sort of VBA code to do that.

 

[Nixhex11]

Dice,
You could try the method I've offered for a similar problem in another thread:
https://www.mrexcel.com/forum/excel...-colour-if-calculated-value-cell-changes.html