Break and Color External Links at once

[actjfc]

Excel friends,

I have a workbook with one sheet with about 2000 external links. I would like to break all the links and simultaneously make the fond dark blue of each broken link. I found this code and it breaks the links of the whole workbook in a fraction of a second. Is there a way to also make the font dark blue at the same speed:

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long
 
    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
 
    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Thanks for any help!

 

[Yongle]

Is this fast enough?

I tried to eliminate false positives with InStr(1, cel.Formula, "\") > 1
- assumes that all your links contain at least one folder and one subfolder and look like "C:\Folder\..."
- amend as suits your data
- if all your links start the same (eg "C:\Test\") then a single test If InStr(1, cel.Formula, "C:\Test\") > 0 could replace the 2 tests in code below which would be faster

Sub GoBlue()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, cel As Range
    For Each ws In ThisWorkbook.Sheets
        On Error Resume Next
            For Each cel In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
                If InStr(1, cel.Formula, "[") > 0 And InStr(1, cel.Formula, "\") > 1 Then cel.Font.Color = -4165632
            Next cel
        On Error GoTo 0
    Next ws
End Sub

 

[actjfc]

Thanks, Yongle, It works but painfully slow. My PC has 16 GB RAM Memory, and it is "not responding". There are too many External Links in one sheet. Maybe, there is an alternative way. The first code above runs in half a second. Is there a code to identify all numbers, not text nor formulas, within one specific sheet and convert the font to dark blue? I do not know how to do it. If you can help, I will appreciate it. Thanks!

 

[Fluff]

How about

Sub actjfc()
    ActiveSheet.UsedRange.SpecialCells(xlConstants, xlNumbers).Font.Color = vbBlue
End Sub

 

[Yongle]

Your PC is painfully slow
- I am not on a fast laptop but my original effort coloured 25,000 links in less than half a second!

Try this
- it will be faster but probably not fast enough
- main change is that the cells are all coloured at the same time

Sub GoBlue2()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cel As Range, rng As Range
    With ActiveSheet
        Set rng = .Cells(Rows.Count, 1)
        On Error Resume Next
            For Each cel In .UsedRange.SpecialCells(xlCellTypeFormulas)
                If InStr(1, cel.Formula, "[") > 0 Then Set rng = Union(rng, cel)
            Next cel
            rng.Font.Color = -4165632
        .Cells(Rows.Count, 1).EntireRow.Delete
        On Error GoTo 0
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Yongle]

Have you considered conditional formatting?
Caution : this may consume too much memory on your PC and slow everything else down

If all your links are in the same drive then you could adapt formula below for use in conditional formatting
=LEFT(FORMULATEXT(B1),5)="='C:\"

 

[actjfc]

Thank you so much! Both solutions worked very well and I learned a lot!

 

[Yongle]

Glad it helped - thanks for your feedback

 

[Fluff]

You're welcome & thanks for the feedback