Highlighting All Formula Cells

[max]

Hi group,

Would like to consult you. For user training purposes, I
need all formula cells in a workbook to be highlighted, to
differentiate these from data cells. It is very tedious
to do this manually.

Is there a vba sub available which can detect & auto-
colour (as a highlight) all formula cells in a workbook ?

TIA
max

[Gary Brown]

Max,
Here's an example, Hope it helps,
Gary L. Brown
Kinneson Consulting
Gary....@Kinneson.com
www.Kinneson.com

'/============================/
Sub ColorFormulas()
ActiveCell.SpecialCells(xlCellTypeFormulas).Select
'yellow background
Selection.Interior.ColorIndex = 36
'red letters
Selection.Font.ColorIndex = 3
End Sub
'/============================/

"max" <demec...@yahoo.com> wrote in message
news:5df401c1680b$cef7cd80$36ef2ecf@tkmsftngxa12...

[Debra Dalgleish]

You can record a macro as you do the following:
1. Select all the cells
2. Choose Edit>Go To
3. Click Special
4. Select Formuala, click OK
5. Select a Fill Colour.
6. Stop the recorder.

The resulting code can be trimmed:

Sub HighlightFormula()
Cells.SpecialCells(xlCellTypeFormulas, 23) _
.Interior.ColorIndex = 6
End Sub

[max]

Thanks, Debra. It works great ! As I want the highlight to
be for the workbook (ie all worksheets), I tried macro-
recording the selection of all worksheets, and came up
with the following (for my test workbook):

Sheets(Array("Sheet1 (2)", "Sheet1")).Select
Sheets("Sheet1 (2)").Activate

How do I modify the above macro so that it will auto-run
your sub for all open worksheets in any workbook ?

Thanks
max

>.
>

[max]

Thanks, Gary. Your sub works great !

As I want the highlight to be for the workbook (ie all

worksheets), what needs to be added so that the sub will
auto-run for all open worksheets in any workbook ?

cheers
max

>.
>

[Roger Govier]

Hi Max

Adapting Debra's formula, put this into the code area for This Workbook

Private Sub Workbook_Open()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.Cells.SpecialCells(xlCellTypeFormulas, 23) _
.Interior.ColorIndex = 6
Next s
End Sub

--
Regards

Roger Govier
Technology 4 U

"max" <demec...@yahoo.com> wrote in message

news:7e0601c16826$50bc53d0$b1e62ecf@tkmsftngxa04...

[Dana DeLouis]

Just to mention... the '23' is the sum of all options to xlCellTypeFormulas.
This is the default setting.
Adapting Debra's formula, you may want to add an "On Error" just in case
there are no formulas.

Sub HighlightFormula()
'In case there are no formulas.
On Error Resume Next
Cells.SpecialCells(xlFormulas).Interior.ColorIndex = 6
End Sub

--
Dana DeLouis Windows Me & Office XP

"Roger Govier" <Roger....@btinternet.com> wrote in message
news:O2gqYbEaBHA.880@tkmsftngp02...

[max]

Thanks, Roger & Dana. Yes, the amended sub works fine.
Much appreciated.

cheers
max

>.
>

[Ian Brown FCA]

Max,

Here's a way to do it without using VBA!
Select cell A1 on any sheet in the workbook
Define the name CellHasFormula (using Insert | Name | Define) as
=GET.CELL(48,!A1)

In Format | Conditional Formatting use a "Formula Is" setting of
=CellHasFormula
to trigger the defined formats.

Apply the conditional formatting as required

HTH

Ian

"max" <demec...@yahoo.com> wrote in message

news:5df401c1680b$cef7cd80$36ef2ecf@tkmsftngxa12...

[Roger Govier]

Hi Ian

Very neat solution

--
Regards

Roger Govier
Technology 4 U

"Ian Brown FCA" <bro...@globalnet.co.uk> wrote in message
news:tunoh45...@corp.supernews.com...

[max]

Thanks, Ian !
cheers
max

>.
>

[Harlan Grove]

Ian Brown FCA <bro...@globalnet.co.uk> wrote...
...

>Here's a way to do it without using VBA!
>Select cell A1 on any sheet in the workbook
>Define the name CellHasFormula (using Insert | Name | Define) as
>=GET.CELL(48,!A1)
>
>In Format | Conditional Formatting use a "Formula Is" setting of
>=CellHasFormula
>to trigger the defined formats.
>
>Apply the conditional formatting as required

WARNING - THIS IS DANGEROUS!

In Excel 97 and 2K, if the cell so formatted or a cell with a formula
referring to CellHasFormula were copied then pasted into a different
worksheet, Excel will CRASH, as in TOTAL DATA LOSS since last save. Try it
for yourself.

I believe this was fixed in XP, but the XLM formula in defined name trick
should always carry a warning.

[max]

Thanks for the alert, Harlan !
Have noted it since I'm using XL97.
cheers.

>-----Original Message-----
>Ian Brown FCA <bro...@globalnet.co.uk> wrote...

>....

>.
>