Conditional replacements with VBA

[schorley]

Howdy all,

I've got two spreadsheets, and old and new style, each with the same
sheets. I've written a (fair) bit of code to take all the data from teh
old style and dump it in the new style (just by copying over formulas
from cell to cell).

Problem is that cell references get messed up in the copying (I dont
use VBA copy or anything, it uses Cell.Formula values).

I also have a list of source/destination sheets and cells [the columns
of this sheet are like [dest sheet | dest cell | source sheet | source
cell]

I have written a macro to go through cells in each sheet to change cell
references from the source cell to the destination cell (eg in the
source a cell has the formula
'=((3761+262-40.5-386.6)/1000)-G8-G13-G14' and the destination cell
should have the formula '=((3761+262-40.5-386.6)/1000)-G7-G12-G13').

The problem is that calculations in the new style spreadsheet also get
updated. I have defined non-calculated cells by black text, and
calculated text by red text (the above example is in black). Is there a
way to get the replace to work ONLY on cells with red text?

Included below is the code for the replacements
Sub FixLinks()
Dim SearchRange As Range
Dim LastCell As String
Dim curSheet As String

LastCell = DetermineLastCell(ActiveSheet).Address(False, False)
Set SearchRange = Range("A1:" & LastCell)
curSheet = ActiveSheet.Name
For i = 3 To DetermineLastCell(ThisWorkbook.Worksheets(2)).Row
If curSheet = ThisWorkbook.Worksheets(2).Cells(i, 1).Value Then
SearchRange.Replace What:=ThisWorkbook.Worksheets(2).Cells(i,
4).Value, Replacement:=ThisWorkbook.Worksheets(2).Cells(i, 2)
End If
Next
End Sub