Hi, I have many cells in Excel file A that are linked to Excel file B (source file). Is it possible for Excel to auto-highlight/color the affected cells in file A when the numbers in file B changes ? I have tried using Private Sub Worksheet_Change(ByVal Target As Range) and Private Sub Worksheet_Calculate() without any success.
I'm not trained in any programming language hence please use simple terms. Thanks !
hi ChinShiong,
This may do the trick.
Please Login or Register to view this content.
Thanks for the reply pike, but it doesn't work. Maybe I'm doing something wrong ?
I opened Excel file A, right click on the sheet name, click "view code", and pasted the code there, and saved the file.
Then I open the source file and changed the value. When I opened file A again to update the links to file B, the numbers are updated, but the font doesn't get highlighted to red. Any thoughts on this ?
no it was just a long shot
but ill look into it
The Change event won't be triggered by a formula, so you would have to use the Calculate event, but that doesn't provide a Target argument telling you what (if anything) changed, so you would have to store the old values of every cell you were interested in and then check all of them whenever the sheet recalculates. This would probably be quite processor intensive (depending on amount of data and how many cells you want to monitor) and might slow your worksheet down too much. How many cells do you want monitored?
Everyone who confuses correlation and causation ends up dead.
I'm looking at hundreds of linked cells.
So the only solution is to break the link for the old file, and run an update for the new file and do a comparison ?
I'm not really sure what you mean by "break the link and run an update"?
Hi ChinShiong,
What about a hidden sheet that when the file is closed copies the Link values and resets the link font colour to black. Then when the file is re-opened, if the values in the Linked cells don't equal the hidden sheet values they change colour?
Hi pike,
That's a great idea. Initially I was thinking of having many sheets, 1 sheet to contain each page of the info that I want linked, but I guess I could do with only 1 sheet for all the info.
Would you have the code for that ?
Hi ChinShiong,
Something along the line of....
Please Login or Register to view this content.
Hi pike, the code doesn't work for me. For the first part, I copy and paste as a macro code, and I'm able to get it to copy and paste values for cell A8 only, after removing the font color line. Possible to copy the entire sheet ?
For the second code, I'm able to get it to work as a macro, but it only compares the value in cell A8. Able to compare between entire sheets ?Sub Macro1()
With Sheets("Sheet1").Range("A8")
.Copy
Sheets("Sheet3").Range("A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
End Sub
Hi ChinShiong
Just so happens I found an example to compare worksheets here in the excel tips
http://www.exceltip.com/st/Compare_t...Excel/477.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks