Visually Detecting Change

[fireslguk]

hi all, is it possible to have a cell change colour only when the cell content (which is a number) is different from when the workbook was last opened.

This cell is linked to another workbook and will always ascend in number.

Yes it will update when the workbook is opened with a new number if the other workbook was changed however i wanted something more visual to detect the change. e.g no change red background, cell changed = green, then when workbook closed reverts to red so you can see change next time round, hope clear


many thanks if any1 could help

[Jacob Hilderbrand]

You could use VBA to update another cell with the previous value, then use Conditional Formatting to make the formatting changes you want.

[vba]
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Range("Z1").Value = Range("A1").Value

End Sub
[/vba]

In this case, let's say A1 has the value you want to check, and Z1 is the cell we will use to store the value. When you save, Z1 will be set to the value of A1, now when you open the workbook later, if the linked value in A1 is different from the latest value in Z1, you can have the cell formatted.

For Conditional Formatting select Formula Is, then use this formula:

=A1<>Z1

Then set the formatting that you would like.