Printing Cell Formulas (97, 2000)

Topic

New Reply

Is there a way to print formulas? For instance, say I have some conditional formulas in
c3:c67. Is there any way to output those formulas to a printer? I don’t need the results of
the formulas, just the formulas. I have searched through the Excel Help, and in this instance
it is of no help. I would have thought this would be part of the auditing — but, if so, I have not
been able to discover how to use it. Any thoughts greatly appreciated.

Reply | Quote

Replies

From the Tools Menu, choose Options and the tab View. Check the box for “formulas”.
This shows the whole sheet as formulae rather than results. You can then print it and return it to results view
Ruth

Reply | Quote

Ctrl ` toggles showing the formulas.

Reply | Quote

You have good answers from Ruth and Michael, but if you are looking specifically for -conditional formulas-, I don’t think Options, View, Formulas, or Ctrl-~ does it. See this Post from Hans Pottel for a msgbox solution to Conditional formats. Partly for my own curiosity, I’ll see if I can rewrite Hans’ code to post the formulas to a new sheet as text. (Don’t wait up.)

Reply | Quote

Edited

Here’s a rewrite of Hans code, ALL credit due to him. Instead of posting to a new sheet, this pastes the formulas as text offset to the right:

Sub ReadCndtlFormats()
Dim rngCFCs As Range, rngCell As Range
Dim intNo As Integer, intCols As Integer
Dim strQ As String, strCFForm1 As String, strCFForm2 As String
‘Type: xlCellValue or xlExpression
‘Operator: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual,
‘ xlNotBetween or xlNotEqual

Application.ScreenUpdating = False
On Error Resume Next
If Selection.Cells.Count = 1 Then
If MsgBox(“Just this cell?”, vbYesNoCancel + vbQuestion, “Conditional Formats”) = vbYes Then
If ActiveCell.FormatConditions.Count > 0 Then
Set rngCFCs = ActiveCell
Else:
Err.Raise 1004
End If
Else
Exit Sub
End If
Else
Set rngCFCs = Selection.SpecialCells(xlCellTypeAllFormatConditions)
End If

If Err.Number = 1004 Then
Beep
MsgBox “No conditional formatting”
Exit Sub
End If

intCols = Application.InputBox(“Specify number of columns” & vbLf & _
“to the right of the selection” & vbLf & ” to paste formulas: “, _
“Conditional Format Formula Copy”, , , , , , 1)
For Each rngCell In rngCFCs
intNo = rngCell.FormatConditions.Count
strQ = “”
For intNo = 1 To intNo
If intNo > 1 Then strQ = strQ & vbLf
strQ = strQ & “Condition ” & Str(intNo) & “: ”
strCFForm1 = rngCell.FormatConditions(intNo).Formula1
strCFForm2 = rngCell.FormatConditions(intNo).Formula2
Select Case rngCell.FormatConditions(intNo).Type
Case xlCellValue
strQ = strQ & “CellValue is ”
Select Case rngCell.FormatConditions(intNo).Operator
Case xlBetween
strQ = strQ & “Between ”
strQ = strQ & strCFForm1 & ” and ” & strCFForm2
Case xlEqual
strQ = strQ & “Equal to ” & strCFForm1
Case xlGreater
strQ = strQ & “Greater than ” & strCFForm1
Case xlGreaterEqual
strQ = strQ & “Greater than or equal to ” & strCFForm1
Case xlLess
strQ = strQ & “Less than ” & strCFForm1
Case xlLessEqual
strQ = strQ & “Less than or equal to ” & strCFForm1
Case xlNotBetween
strQ = strQ & “Not between ” & strCFForm1 & ” and ” & strCFForm2
Case xlNotEqual
strQ = strQ & “Not equal to ” & strCFForm1
End Select
Case xlExpression
strQ = strQ & “Formula is ” & strCFForm1
End Select
Next intNo
rngCell.Offset(0, intCols).Value = “Cell ” & rngCell.Address & “: ” & “Conditional Format ” & vbLf & strQ
With rngCell.Offset(0, intCols).EntireColumn
.ColumnWidth = 100 ‘ annoying
.WrapText = True
.AutoFit
End With
Next rngCell
Application.ScreenUpdating = True
End Sub

Reply | Quote