Links to Hard Values

[Actuariojf]

I usually develop large excel workbooks that have just one output sheet which is linked with all other sheets in the workbook. However, when I have to deliver this output to other people I copy the output page to a new book, but all the links remain there as "=Cath ... ", so now I have to locate manually all the linked cells and do a copy-paste special as value, after that I change each one of the old linked cell to BOLD and colored them to BLUE.

My question, Can somebody do a macro that can locate automatically anywhere all the linked cells in an entire sheet, highlight these cells as blue bold and make the cell a hard values, so there will not be more links and all old linked cells will be easily identifiable in the sheet?

Thanks

Actuariojf

 

[Andrew Poulsom]

Yes.

Sub Test()
    Dim c As Range
    For Each c In Selection.SpecialCells(xlCellTypeFormulas, 23)
        If InStr(1, c.Formula, "[") <> 0 Then
            c.Value = c.Value
            c.Font.Bold = True
            c.Font.ColorIndex = 5
        End If
    Next c
End Sub

 

[Actuariojf]

Thanks a lot! It works great!