PDA

View Full Version : highlighting precedent cell



jathin_j
06-28-2012, 04:16 AM
I am always working with more than one excel worksheets and linking figures from one sheet to another. what help i need is a VB function which will highlight all the direct precedent cells of a current active cell even if the precedents are lying in a different working sheet.


Thanking you

Tinbendr
06-28-2012, 10:34 AM
Welcome to the board!

If you've posted this question on other boards, please provide that link.

Sub test()

With ActiveCell
.Precedents.Interior.ColorIndex = 3 'Red
End With
End Sub

CodeNinja
06-28-2012, 02:51 PM
To see precedents off worksheet, Bill Manville came up with a solution:


Sub FindPrecedents()
' written by Bill Manville
' With edits from PaulS
' this procedure finds the cells which are the direct precedents of the active cell
Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
Dim stMsg As String
Dim bNewArrow As Boolean
Application.ScreenUpdating = False
ActiveCell.ShowPrecedents
Set rLast = ActiveCell
iArrowNum = 1
iLinkNum = 1
bNewArrow = True
Do
Do
Application.Goto rLast
On Error Resume Next
ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
If Err.Number > 0 Then Exit Do
On Error Goto 0
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
bNewArrow = False
If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
' local
stMsg = stMsg & vbNewLine & Selection.Address
Else
stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
End If
Else
' external
stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
End If
iLinkNum = iLinkNum + 1 ' try another link
Loop
If bNewArrow Then Exit Do
iLinkNum = 1
bNewArrow = True
iArrowNum = iArrowNum + 1 'try another arrow
Loop
rLast.Parent.ClearArrows
Application.Goto rLast
MsgBox "Precedents are" & stMsg
Exit Sub
End Sub

Pretty clever, he shows the precedents and then follows them with navigateArrow. Anyway, this could easily be adjusted to highlight the cells you want or made into a function to get to the cell you want to highlight.

If you need me to do it for you, I can, just not today.

mikerickson
06-28-2012, 05:07 PM
This will list all the precedents of the active cell, on sheet, off sheet, off workbook, even references to closed workbooks.

http://www.vbaexpress.com/forum/showthread.php?t=19348&page=2&highlight=precedents