ExcelBanter - View Single Post - change color of all cells with formula or are part of a formula

Ron Rosenfeld

On Sun, 27 Jan 2008 09:37:27 -0800 (PST), wrote:

is it possible to change the color of all cells that contain a formula
or are part of a formula???

if so, how would i do that?

As I read this, what do you mean by cells that "are part of a formula"?

If you mean what I think, then perhaps:

=========================
Sub ColorFormulas()
On Error Resume Next
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
End Sub
============================

Of course, as written, if you make changes, the already colored cells may not
change.

It would be simplest to first set the format to "none" for the worksheet, and
then just color the formulas and precedents. But this may not be appropriate.

==============================
Sub ColorFormulas()
On Error Resume Next
Cells.Interior.ColorIndex = xlNone
With Cells.SpecialCells(xlCellTypeFormulas)
.Interior.Color = vbYellow
.Precedents.Interior.Color = vbRed
End With
End Sub
===============================

--ron