Phil,

This looks like the same question you asked on May 27th, and this was my
response then

Apart from no 1, which I can't see that it does, this covers the other
points

Sub ListFormulas()
'From John Walkenbach
List formulas, cell addresses and values in a newly created worksheet
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

'Create a range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

'Exit if no formulas found
If FormulaCells Is Nothing Then
MsgBox "No Formulas, or the sheet is protected."
Exit Sub
End If

'Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

'Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

'Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address(RowAbsolute:=False,
ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
If InStr(1, Cell.Formula, "[") 0 Then
Cells(Row, 2).Font.ColorIndex = 3
End If
Row = Row + 1
End With
Next Cell

'Adjust column widths
With FormulaSheet
.Columns("A:A").AutoFit
With .Columns("B:C")
.ColumnWidth = 45
.WrapText = True
End With
.Rows("1:1000").AutoFit
End With

Application.StatusBar = False
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Hageman" wrote in message
...
This code creates a new worksheet in a workbook and lists all formulas
contained in the target worksheet. Could someone help modify this code to do
the following:
1. The resulting list contains many rows where there are no formulas -
would like to omit the blank (no formula) rows.
2. Make the column width for column B and C be "45" (rather than the
existing auto fit).
3. When the data in column B or C exceeds the "45" width, expand row
height to fit the text, and wrap text.
4. When the formula in column B references a workbook other than the
current workbook, make font red in all cells in that row.

Any help would be much appreciated.
Thanks,
Phil

Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

'Create a range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

'Exit if no formulas found
If FormulaCells Is Nothing Then
MsgBox "No Formulas, or the sheet is protected."
Exit Sub
End If

'Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

'Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

'Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address(RowAbsolute:=False,
ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

'Adjust column widths
FormulaSheet.Columns("A:C").AutoFit
Application.StatusBar = False
End Sub