Identifying links from other sheets (Excel 2000)

[ChrisGreaves]

And this is how I make a visual identification of cells which draw values from other sheets.
Crudely put: If there is an exclamation mark, I assume that it is a sheet reference.
"Close enough for government work"

Code: Select all

Function blnStainfromInput(rng As Range, lngColorIndex As Long) As Boolean
    ''' Stain all cells CONTAINING A LINK to a background color
    '''     RANGE   Of cells to be stained
    '''     LONG    Color Index = 1:56 where 2=White; -4142=xlNone
    On Error GoTo Failed
    Dim rngCell As Range
    For Each rngCell In rng
        ''' rngCell.Select
        If rngCell.HasFormula Then
            If InStr(1, rngCell.Formula, "!") > 0 Then
                rngCell.Interior.ColorIndex = lngColorIndex
                blnStainfromInput = True
            Else
            End If
        Else
        End If
    Next rngCell
Failed:
'Sub TESTblnStainfromInput()
'    Call blnStainfromInput(ActiveSheet.UsedRange, 5)
'End Sub
End Function

(edited after reading Hans's comment)

[HansV]

"Clear all cells"?

The line

rngCell.Select

isn't necessary - you can change the color of a cell without selecting it.

[ChrisGreaves]

"Clear all cells"? ... rngCell.Select ...

I know, I know ...
But at this hour of the morning and running on only 2 I get excited easily when something works and rush to get it published before anyone else ... (grin!)
Thanks for sweeping up after the horse.