Highlighting a Change from a linked workbook *

[fireslguk]

Whats the simplist way of highlighting a change to a cell which is contains a different value to when it was last opened to indicate a new value (Change).

Ill explain :-

7 Workbooks in total being different departments.

1 Workbook being a status page containing totals and links to these others.

When this status one is opened if the link cells have changed in these other depts workbooks then a cell next to this link or the link cell itself formats to green background. If no change then it will stay say yellow.

 

[GlennUK]

You could copy values to a reference area before you close the book, then compare against those values when reopening and updating links.

 

[fireslguk]

struggling can you giss a clue, doing a copy before close and then cond format but no still no joy

 

[GlennUK]

To help I need details ... precise details. What are you doing exactly?

 

[fireslguk]

sheet 1 a1 = value 27 say

sheet 2 (the status sheet) b1 = sheet1 a1 (being value 27 (A link)

code in sheet 2 = before close copy b1 to b2 save then quits

sheet 2 cond format b1 = if b1 cell value not equal to b2 then green

on opening sheet2 problem = b1 updates and so does b2 same time therefore no value change.

 

[GlennUK]

copy b1 to b2

that should be copy values to b2.

 

[fireslguk]

values ?

how do i write that line in vba

Range("b1").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
Application.Quit

 

[GlennUK]

I'd do:

Range("C1").Value=Range("B1").Value

it's more efficient that doing PasteSpecial Paste:=xlValues