VBA - See if a cell has a formula that refers to another sheet

Hello

I am simply trying to make a macro that colours cells in the current selection in blue, for those cells having a formula with external (i.e. other sheets or workbooks) references. The current code is below, but where I am stuck is: how can i find a way to define "hasformula" better and add the "external link" component. Any idea? That'd be super helpful ! Thanks a lot

Sub FontColor()

'FontColor Macro
'Color = -39424

If Selection.HasFormula Then
    With Selection.Font
        .Color = -16751104
        .TintAndShade = 0
    End With

End If

End sud

As far as I know there isnt a property or method that evaluates a formulas contents for this. You would need to take the formula from the cell and break it down to evaluate it yourself. As far as I can tell conditional formatting also doesnt have a way to evaluate this.

I think what you are asking to do is simple to say but would be complex to do. Judging from your question I would guess we cannot presume or look for only a certain sheet or workbook?

The external workbook part can be done (I think I have done that before). I would imagine the hard part is the evaluating if a sheet reference is within the same workbook.

Why is it you are trying to do this, maybe there is an easier way? Also if your not sure what version of Excel you have, please try and find out so we dont waste your time (and ours) recommending things that are not options for you.

So I checked and do have code to check if a formula has external references in it. This alone is pretty complex (as I was extracting them and checking if they were valid links still). Consider the following:

• A formula could have multiple external references within it
• The syntax for an external reference is different if the reference is also a table
• Its almost impossible in my opinion to evaluate say the result of an INDIRECT as an external reference (as the resulting reference only exists during calculation).

My code is long, complex and quite frankly would likely not help anyone to just dump it here so I will share the pieces I think will help. I would first evaluate if the "string" of the formula has within it any of the following (".xlsx", ".xlsm", ".xlsb", ".xls") as any external reference to another workbook will have the file name in it ending with one of those extensions (maybe more, but those are the common ones).

This could simply be an IF OR statement like (after having checked Hasformula):

If instr(1, range.formula, ".xlsx") > 0 OR instr(1, range.formula, ".xlsm") > 0 Then

That alone may be enough for you to consider the formula having an external reference and coloring it. I presume it doesnt matter to you how many external references are in the cell.

A sheet would be more difficult to evaluate as the syntax is different depending on if the sheet name has spaces or not and is also uses pretty common characters/syntax that may be hard to set apart from other text in a formula/cell. With a space the syntax is

'sheetname'!

so you may be able to look for the single quote flowed by the quote with the exclamation and grab everything in between. However without a space its just a sheetname! so the only character that signifies it is a ! after the name, which makes it very difficult to tell when the sheet name starts and ends.

That part may take some research to see if there is a viable way.

thanks a lot both, this is helpful. I will read trhrough tonight and give it a go with your recommendations!

I have used this, but replacing "Sheet" by "!" which is a caracter that only ever appears in a formula when referencing to another sheet (whether same or different workbook), and so far it works !! Thanks a lot again both, truly helpful. Don't hesitate to comment if you think there is any flaw of course

Sub FontColor()
    'FontColor Macro
    'Color = -39424
    Dim rng As Range
    For Each rng In Selection
        If rng.HasFormula = True And InStr(rng.Formula, "!") > 0 Then
            With rng.Font
                .Color = -39424
                .TintAndShade = 0
            End With
        End If
    Next rng
End Sub

makes sense, brilliant. Thanks !
I don't find the "solved" button in this forum, need a new pair of glasses

thank you, done