How to Highlight Formula Cells

[blank]

Would some kind soul please tell me how to make spreadsheets highlight cells
which contain formulas (to ensure I am very careful when making changes)?
(Excel 2003).

 

[Bob Phillips]

If you do a Ctrl-` that will show the formula in the cell rather than the
results of the formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ian]

An alternative to Bob's suggestion is to unlock the cells without formulae,
then protect the sheet. This will not allow you to edit locked cells.

 

[Saruman]

Why not protect the sheet after unlocking all cells you do want to change
and leave the formula cells locked?

Highlight all cells you need to change, right click on one of them and click
on the Format Cells menu.

Go to the Protection Tab and remove the tick from the Locked Box. OK out of
it.

Now go Tools, Protection, Protect sheet.

Enter password if reqd and select what you want to be able to change. If you
dont want to select the formula cells, then dont allow users to select
locked cells.

If a password is used, you will have to repeat the password, then OK out of
it.

Now you cant select the formula cells to accidentally change them.

 

[blank]

Thanks folks, but I dont want to protect the cells, just highlight all cells
that have a formula, so I am aware of the fact. Locking is too cumbersome
when quantities in cells change frequently. All those arrows are also not
what I want. I just want all cells that have formulaes to automatically
highlight with a colour. Is that possible without a lot of resorting to the
colour pallette each time?

 

[Bob Phillips]

You could create a UDF

Public Function IsFormula(rng as Range)
IsFormula = rng.HasFormula
End Function

and the use conditional formatting by selecting all cells and using a
formula of =IsFormula(A1)

but this will get very big and heavy on resources.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[blank]

OK, sounds like I may as well persevere with the colour palette method.
(Not very sophisticated, but then, neither am I.

 

[Ken Wright]

If your spreadsheet is already complete and you don't need to keep changing
cells to be highlighted, then just select the entire sheet and do edit / Go
To / Special / Formulas and then hit one of the palette colours...

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[blank]

EXCELLENT !!!!!!!!!! EUREKA !!!!!!!!!! Thats is exactly the answer I
wanted to hear. Thank you very much.

 

[Ken Wright]

LOL - you're welcome

Regards
Ken................