Solved: Highlight cell from link

[ericc]

Hello

I use Excel 2003.

In a workbook, I have 2 sheets. I link some cells in the Sheet2 with cells from Sheet1. (Paste as Link)

I'm searching a way to "Highlight" the cells in Sheet1 when a link to this cell exist in Sheet2.
Just to be sure that I don't forget to link a cell from Sheet1.
(So Sheet1 is the source and Sheet2 the destination)

If in a cell of Sheet2, I have : "=Sheet1!$B$25" , I would like that the cell "B25" in Sheet1 have a different background colour by example or whatever as long as I can distinguish it

Do you think it's possible ?

Thanks in advance for your help

ericc

[hardlife]

Hi, this code works if it is simple formula as You mentioned "=Sheet1!$B$25"

on more complex formula necessary to developed further check

wish You GOOD LUCK and good night,

Pavel Humenuk



Sub color()

Sheets("List1").Cells.Interior.ColorIndex = xlNone

Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select

If Not Selection Is Nothing Then
For Each cell In Selection
MsgBox cell.Formula
MsgBox "address of formula " & cell.Address(0, 0), , _
cell.Parent.Name
MsgBox Len(cell.Formula)
MsgBox Mid(cell.Formula, 8, Len(cell.Formula))

If Mid(cell.Formula, 1, 7) = "=List1!" Then
MsgBox cell.Formula
Sheets("List1").Range(Mid(cell.Formula, 8, Len(cell.Formula))).Interior.ColorIndex = 6
Else
End If

Next
End If

'Range("A1").Select
Application.Goto Reference:="R1C1"

End Sub

[ericc]

Excellent !!
This is exactly what I need

Thank you

ericc

[AZIQN]

I know its solved already, but I thought I'd edit hardlife's version so the user does not need to click through numerous message boxes to highlight the cells. Hope this helps.


[vba]
Sub color()
Sheets("List1").Cells.Interior.ColorIndex = xlNone
Sheets("List2").Activate
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
If Not Selection Is Nothing Then
For Each cell In Selection
If Mid(cell.Formula, 1, 7) = "=List1!" Then

Sheets("List1").Range(Mid(cell.Formula, 8, Len(cell.Formula))).Interior.ColorIndex = 6
Else
End If
Next
End If
Application.GoTo Reference:="R1C1"
End Sub
[/vba]
REVISED -original code written by hardlife