Break and Color External Links at once

actjfc

Active Member
Joined
Jun 28, 2003
Messages
414
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:

VBA Code:
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 
Last edited:

actjfc

Active Member
Joined
Jun 28, 2003
Messages
414
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub actjfc()
    ActiveSheet.UsedRange.SpecialCells(xlConstants, xlNumbers).Font.Color = vbBlue
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Have you considered conditional formatting?
Caution : this may consume too much memory on your PC and slow everything else down :cautious:

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

Active Member
Joined
Jun 28, 2003
Messages
414

ADVERTISEMENT

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Glad it helped - thanks for your feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,027
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,983
Messages
5,767,438
Members
425,413
Latest member
ccfam04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top