Highlight the cells referenced in the active cell formula
 

I want to have the cells that are referenced in the active cells formula
highlight. For example if I have column A cell 1 with a formula of
=sum(b2+c3+g6) I would like for the cells b2, c3, g6 to highlight yellow. The
formula changes for each cell as do the referenced cells. Does anyone know if
this is possible?

Highlight the cells referenced in the active cell formula
 

If you want to give up Undo, colored cells, and the ability to paste on the
sheet, in the selectonchange event, you could write code to uncolor all
cells, then use the Target(1).DirectPrecedents to color the cells.

Demoing directprecedents from the immediate window.

? activecell.Formula
=SUM(B2+C3+G6)
? activecell.DirectPrecedents.Address
$B$2,$C$3,$G$6

--
Regards,
Tom Ogilvy


"Charles" wrote:

I want to have the cells that are referenced in the active cells formula
highlight. For example if I have column A cell 1 with a formula of
=sum(b2+c3+g6) I would like for the cells b2, c3, g6 to highlight yellow. The
formula changes for each cell as do the referenced cells. Does anyone know if
this is possible?

Highlight the cells referenced in the active cell formula
 

Thanks for the suggestion. I tried the code below,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1", "IV65536").Interior.ColorIndex = xlNone
If Range(ActiveCell.Address(RowAbsolute:=False,
COLUMNABSOLUTE:=False)).Formula Like "=*" Then

Range(ActiveCell.Address(RowAbsolute:=False,
COLUMNABSOLUTE:=False)).DirectPrecedents.Select

With Target.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
End Sub

I'm not sure if that is what you had in mind or if there is a cleaner way to
do this process. I would also like to have the starting cell be the active
cell after the highlights are done. Any ideas/suggestions would be great!



"Tom Ogilvy" wrote:

If you want to give up Undo, colored cells, and the ability to paste on the
sheet, in the selectonchange event, you could write code to uncolor all
cells, then use the Target(1).DirectPrecedents to color the cells.

Demoing directprecedents from the immediate window.

? activecell.Formula
=SUM(B2+C3+G6)
? activecell.DirectPrecedents.Address
$B$2,$C$3,$G$6

--
Regards,
Tom Ogilvy


"Charles" wrote:

I want to have the cells that are referenced in the active cells formula
highlight. For example if I have column A cell 1 with a formula of
=sum(b2+c3+g6) I would like for the cells b2, c3, g6 to highlight yellow. The
formula changes for each cell as do the referenced cells. Does anyone know if
this is possible?