Macro for Tracing Precents and Formula Auditing

[MachaMacha]

Hello,

I have two requests for help in terms of formula aduuditing in Excel.

#1 F2

You can see the components of an equation by hitting F2 on the cell that the formula is in. The precedent cells are outlined in different colors.

Is there any macro or way to make the precedent cells (once F2 is hit) larger/zoomed in and for the colored outlines to be thicker on these precendent cells?

I am looking at many formulas in humongous spreadsheets that extend out to column IV so this would allow me to look at the precedent cells more easily. Also if the cells are really spread out, for example IV5=A500+BC22-CF4000/IN543 is there a way to hide the columns in between so I can only see columns IV, A, BC, CF, and IN?


#2 Tracing Many Precedents

I am working in one workbook with about 20 sheets. Each cell has a formula in it, the precedent cells are also formulas - so that there are many layers or dimensions of precedents to each cell. Is there a macro I can run on a selected cell to show each level/layer of precedent cells and the precedent cells' locations? I would like to get to the level where data/numbers are actually input into the spreadsheet.

Thanks, MachaMacha

[Bob Phillips]

How about this for #1

[vba]

Public Sub HighlightPrecedents()
Dim rng As Range
Dim mpArea As Range
Dim mpcell As Range

For Each mpArea In ActiveCell.DirectPrecedents.Areas

For Each mpcell In mpArea

With ActiveSheet.Shapes.AddShape( _
msoShapeRectangle, mpcell.Left, mpcell.Top, mpcell.Width, mpcell.Height)
.Fill.ForeColor.SchemeColor = 26
.Fill.Transparency = 0.5
.Line.Weight = 1.5
.Line.DashStyle = msoLineDash
.Line.Style = msoLineSingle
.Line.Transparency = 0.75
.Line.Visible = msoTrue
.Line.ForeColor.SchemeColor = 64
.Line.BackColor.RGB = RGB(255, 255, 255)
End With
Next mpcell
Next mpArea
End Sub
[/vba]

[MachaMacha]

Thanks xld, that takes care of #1!