' Replaces all the "=" with "#" or vice versa depending on first cell
' This is useful for copying cells that contain formulas with cell references that you don't want to change when you move them.  For example, transposing, copying between workbooks
'

Sub toggleEqualsSign()
'
' toggleEqualsSign Macro

Dim replaceWhat As String
Dim replaceWithThat As String
Dim sel_rng As Range
Dim first_formula As String
Dim lRows As Long
Dim lCol  As Long
Dim r As Long
Dim c As Long

Dim replaceWith(0 To 1) As String


Application.ScreenUpdating = False

Set sel_rng = Selection
lRows = sel_rng.Rows.count
lCol = sel_rng.Columns.count

'go through each cell
For r = 1 To lRows
    For c = 1 To lCol
        first_formula = sel_rng(r, c)
        replaceWhat = Left(first_formula, 1)
        If replaceWhat = "#" Or sel_rng(r, c).HasFormula Then GoTo StartReplace:
    Next c
Next r

If r = lRows + 1 And c = lCol + 1 Then
    Exit Sub
End If

StartReplace:
If replaceWhat = "#" Then
    replaceWithThat = "="
Else
    replaceWhat = "="
    replaceWithThat = "#"
End If

    Selection.Replace What:=replaceWhat, replacement:=replaceWithThat, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Application.ScreenUpdating = True
End Sub