Conditional Formats - Forumlas

[VBA Noob]

Hi all,

I'm using the tip on the attached link to highlight cells wit
formulas. At home it opens fine but at work a co-worked and I both ge
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 Noo

 

[Max]

.. 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]

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
'----

 

[Biff]

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]

Thanks for all the replies however I was trying to avoid macro's as th
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
error as there are some unknowns which will be amended by the user t
True or False

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

Any other ideas ??

VBA Noo

 

[Biff]

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]

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