|Never mind this post..although this method should work, see the next response for a slightly better way to accomplish your goal.|
OK, I have to make few assumptions here. Correct me if I am wrong because there are probably other ways to do this depending on the layout of your data. This is just a first shot and I would need to know more specifics to clean it up a bit. If you like the concept, we can work on it some more. It's far from perfect.
First, I'll assume that the layout of your own sheet does not match the layout of the external data. In other words, the formulae in Sheet1 isn't nice and "neat" like this...
A1 contains =SHEET2!A1
B1 contains =SHEET2!B1
but more like:
A1 contains =SHEET2!O45
B1 contains =SHEET2!C11
i.e. no real one-to-one pattern.
I'll also assume that you have enough room in Sheet1 to fit all of the external data as well as your own data. I'll explain why later.
And finally, I'll assume that the simple formula you posted - where only one cell is referenced - is really what you are using.
Here's what I did...
I set up Sheet1 with a bunch of formulae referenceing cells in Sheet2, like I assume you have now.
I selected the cells with the formulae in Sheet1 and made it a Named Range - I named it myRng.
I then selected all of the data on Sheet2 and cut/pasted it into Sheet1, out of the way where it won't bother me. In my test, I started the paste in M1. All of the colors should have been retained.
This process (cut/paste) should have updated all of the formulae so that they now reference the data in Sheet1, not Sheet2.
Next, right click the sheet tab for Sheet1 and choose View Code.
Paste this code into the pane that opens.
Private Sub Worksheet_Change(ByVal Target As Range)
'Error handler for cells without precedents
On Error Resume Next
'Check location of change
If Not Intersect(Target, Range("$M$1")) Is Nothing Then
'If new data was pasted in, loop through precedents and set Font Color
For Each myCell In Range("myRng")
myCell.Font.ColorIndex = myCell.Precedents.Font.ColorIndex
From now on, paste your external data into the same place in Sheet1, not Sheet2.
What this does is monitor Sheet1 for changes. In my example, if the change it sees started in M1, it means you pasted in new data. It will ignore any changes to the sheet that do not include M1.
The code will then loop thorugh the cells in myRng, "read" the formula in each, determine the "Precendent" for that cell (the cell that the formula refers to) and then set the Font Color to the same Font Color as the Precendent.
The reason the data has to be in Sheet1 is that the VBA Precedent property only works on cells in the same sheet, so it can't "locate" the Precedents if they are on a different sheet.
If the layout of your sheets is in some kind of sequential order, then it may be possible to loop through the cells based on their addresses (instead of their precedents) and not have to move the data into Sheet1. You'd have to supply those details before we can offer something else.
Let me know if that is of any help.