Conditional Formats - Forumlas
 

Hi all,

I'm using the tip on the attached link to highlight cells with
formulas. At home it opens fine but at work a co-worked and I both get
a macro warning saying there was a Macro V4 on the sheet.

Does anyone know why or an alternative to highlighting the formulas

http://www.j-walk.com/ss/excel/usertips/tip045.htm

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563079

Conditional Formats - Forumlas
 

.. an alternative to highlighting the formulas

Perhaps a viable alternative ..
Press F5 Special Check "Formulas" OK
will select all formula cells on the sheet at one go
Then just format to taste (Format Cells)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"VBA Noob" wrote:

Hi all,

I'm using the tip on the attached link to highlight cells with
formulas. At home it opens fine but at work a co-worked and I both get
a macro warning saying there was a Macro V4 on the sheet.

Does anyone know why or an alternative to highlighting the formulas

http://www.j-walk.com/ss/excel/usertips/tip045.htm

Conditional Formats - Forumlas
 

Or, we could assign the Sub FormatFormulaCells() below to a shortcut key

I recorded* a macro as the earlier steps were done manually,
the F5 Special ..., Format Cells Font dark blue/bold ...
then slightly edited the recorder's output
*via: Tools Macro Record New Macro

To install the sub:

In Excel,
Press Alt+F11 to go to VBE
Click Insert Module
Copy & paste the sub into the code window
Press Alt+Q to get back to Excel

To assign the sub to a shortcut key:

In Excel,
Press Alt+F8 to bring up the Macro dialog
Select "FormatFormulaCells" click Options,
then assign a shortcut key, say: Ctrl+k
Click OK, then dismiss the dialog (press Cancel)

Test it out ... in any sheet with formulas,
just press the shortcut combo: Ctrl+k
and all the formula cells in the sheet
would be accordingly formatted (dark blue/bold)

Adapt to suit ..

'-----
Sub FormatFormulaCells()
'Selects & formats formula cells on activesheet
On Error Resume Next
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.NumberFormat = "0.00"
With Selection.Font
.FontStyle = "Bold"
.ColorIndex = 5 'dark blue
End With
Selection.Interior.ColorIndex = xlNone
End Sub
'----
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Conditional Formats - Forumlas
 

I've used that technique many times and have never recieved that message. It
may have something to do with your macro security settings. My security
level is usually set to low so I never get warnings.

The only other alternative is to use a simple UDF (which doesn't help if you
don't want to/can't use macros):

http://tinyurl.com/k3ttk

Biff

"VBA Noob" wrote in
message ...

Hi all,

I'm using the tip on the attached link to highlight cells with
formulas. At home it opens fine but at work a co-worked and I both get
a macro warning saying there was a Macro V4 on the sheet.

Does anyone know why or an alternative to highlighting the formulas

http://www.j-walk.com/ss/excel/usertips/tip045.htm

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563079

Conditional Formats - Forumlas
 

Thanks for all the replies however I was trying to avoid macro's as the
end user can disable them.

Maybe there's a different tact I can use.

I've got a table of True or False answers. A few lines will return a
error as there are some unknowns which will be amended by the user to
True or False

I tried to format using the IsText function but unless the user enters
a ' at the start it see's it as a logic statement.

Any other ideas ??

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563079

Conditional Formats - Forumlas
 

There's a way to do this without macros or GET.CELL but it's not really
foolproof so, caveat emptor!

First, let's see if I fully understand what you're doing:

You have a range of formulas that return boolean TRUE or FALSE ?

Sometimes the formulas will return errors like #VALUE! (or whatever) ?

When a formula returns an error a user will manually enter true or false in
the cell thus overwriting the formula ?

You want to use conditional formatting to identify the cells that still have
a formula entered in them ?

Assuming all the above is correct........

*AFTER* the formulas have been entered set data validation:

CUSTOM

Formula: =ISTEXT(A1)

Set the error alert message to something like this:

You must enter uppercase 'TRUE or 'FALSE.
Precede the entry with a '

Set the conditional formatting:

Formula Is: =ISLOGICAL(A1)

Also, format the entire range to CENTER so everything will look uniform.

The data validation will ensure the user inputs a TEXT entry and you can
still test for a formula by checking the result is a boolean.

Once again, this is not foolproof!

Biff

"VBA Noob" wrote in
message ...

Thanks for all the replies however I was trying to avoid macro's as the
end user can disable them.

Maybe there's a different tact I can use.

I've got a table of True or False answers. A few lines will return a
error as there are some unknowns which will be amended by the user to
True or False

I tried to format using the IsText function but unless the user enters
a ' at the start it see's it as a logic statement.

Any other ideas ??

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563079

Conditional Formats - Forumlas
 

Thanks Biff,

You followed all right. However again being picky I want to avoid an
inexperience user have to enter the ' before TRUE or FALSE.

I've decided to live with the double warning message I'm getting

Your help is always appreciated. Keep up the good work

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=563079