Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
---
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste Special Formats Generated by Conditional Formatting ExcelMonkey Excel Discussion (Misc queries) 6 May 22nd 06 07:10 PM
Why do conditional formats appear by themselves in Excel 2003? Rambling Syd Rumpo Excel Discussion (Misc queries) 0 March 23rd 06 12:10 PM
how do I apply more than 3 conditional formats in excel chetwyndthomas Excel Discussion (Misc queries) 1 January 30th 05 04:24 PM
how do i get more than three conditional formats in excel Tom_t Excel Worksheet Functions 1 December 15th 04 07:35 AM
Conditional Formats in Excel DaveB Excel Worksheet Functions 2 November 15th 04 07:36 AM


All times are GMT +1. The time now is 11:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"