Formulae, conditional formatting & macro security

[Kevin Lucas]

I've used conditional formatting based on a simple formula like =$B6=1. Now
Excel warns me of macro viruses and I have to choose whether or not to
enable macros before opening this spreadsheet. I'm pretty sure this formula
is the cause because I've tested it in a spreadsheet that contains nothing
else except the two cells under test (B6 and the cell I'm formatting). If I
change the formula to a constant and use "cell value is" then the macro
warning goes away.

I certainly haven't created any macros in the workbook and if there were any
presumably the warning wouldn't go away when I remove the formula.

Are formulae treated as macros by Excel security? And is there any way to
achieve conditional formatting off formulae (needed to format one cell on
the basis of the value in another) without incurring security warnings?

While I could change my own security settings I'm not at liberty to change
those of colleagues who are using such a spreadsheet and who don't want the
warnings to appear each time the workbook is opened.

Thanks for any ideas you have.

Kevin Lucas

 

[Jim Rech]

You should always mention your Excel version in a post.

I tried Excel 97 through Excel 2003 and couldn't reproduce the problem. If
you have a simple workbook that demonstrates the issue you can send it to
me.

--
Jim Rech
Excel MVP
| I've used conditional formatting based on a simple formula like =$B6=1.
Now
| Excel warns me of macro viruses and I have to choose whether or not to
| enable macros before opening this spreadsheet. I'm pretty sure this
formula
| is the cause because I've tested it in a spreadsheet that contains nothing
| else except the two cells under test (B6 and the cell I'm formatting). If
I
| change the formula to a constant and use "cell value is" then the macro
| warning goes away.
|
| I certainly haven't created any macros in the workbook and if there were
any
| presumably the warning wouldn't go away when I remove the formula.
|
| Are formulae treated as macros by Excel security? And is there any way to
| achieve conditional formatting off formulae (needed to format one cell on
| the basis of the value in another) without incurring security warnings?
|
| While I could change my own security settings I'm not at liberty to change
| those of colleagues who are using such a spreadsheet and who don't want
the
| warnings to appear each time the workbook is opened.
|
| Thanks for any ideas you have.
|
| Kevin Lucas
|
|

 

[Jim Rech]

I get the macro warning regardless of the conditional formats.

The reason for it is the macro in the Sheet1 code module. Right click the
tab and pick View Code. It's true that Sub Worksheet_SelectionChange has no
code in it but the macro virus detection device in Excel doesn't evaluate
the code, it just looks to see if there is any.

 

[Kevin Lucas]

Jim

Thanks for picking this up. I'm unclear where this macro comes from. As it
doesn't contain any code would you expect I can just delete it?

Thanks for your help

Kevin

 

[Dave Peterson]

I'm not Jim, but if there's nothing in that worksheet module that does anything,
then you can select all the text (ctrl-a) with that code window active and hit
the delete key. (You can't delete the worksheet module itself.

Debra Dalgleish has nice instructions at:
http://www.contextures.com/xlfaqMac.html#NoMacros

 

[Jim Rech]

What Dave said, Kevin. That code does nothing (except raise the warning).

--
Jim Rech
Excel MVP
| Jim
|
| Thanks for picking this up. I'm unclear where this macro comes from. As it
| doesn't contain any code would you expect I can just delete it?
|
| Thanks for your help
|
| Kevin
|
| | > I get the macro warning regardless of the conditional formats.
| >
| > The reason for it is the macro in the Sheet1 code module. Right click
the
| > tab and pick View Code. It's true that Sub Worksheet_SelectionChange
has
| no
| > code in it but the macro virus detection device in Excel doesn't
evaluate
| > the code, it just looks to see if there is any.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > > Sorry - I'm using Excel 2003 and I've attached a test workbook that
| > > manifests the problem. My Excel Macro Security level is set to medium.
| > >
| > > Thanks
| > >
| > > Kevin
| > >
| > > | > >> You should always mention your Excel version in a post.
| > >>
| > >> I tried Excel 97 through Excel 2003 and couldn't reproduce the
problem.
| > >> If
| > >> you have a simple workbook that demonstrates the issue you can send
it
| to
| > >> me.
| > >>
| > >> --
| > >> Jim Rech
| > >> Excel MVP
| > >> | > >> | I've used conditional formatting based on a simple formula like
| =$B6=1.
| > >> Now
| > >> | Excel warns me of macro viruses and I have to choose whether or not
| to
| > >> | enable macros before opening this spreadsheet. I'm pretty sure this
| > >> formula
| > >> | is the cause because I've tested it in a spreadsheet that contains
| > > nothing
| > >> | else except the two cells under test (B6 and the cell I'm
| formatting).
| > > If
| > >> I
| > >> | change the formula to a constant and use "cell value is" then the
| macro
| > >> | warning goes away.
| > >> |
| > >> | I certainly haven't created any macros in the workbook and if there
| > >> were
| > >> any
| > >> | presumably the warning wouldn't go away when I remove the formula.
| > >> |
| > >> | Are formulae treated as macros by Excel security? And is there any
| way
| > > to
| > >> | achieve conditional formatting off formulae (needed to format one
| cell
| > > on
| > >> | the basis of the value in another) without incurring security
| warnings?
| > >> |
| > >> | While I could change my own security settings I'm not at liberty to
| > > change
| > >> | those of colleagues who are using such a spreadsheet and who don't
| want
| > >> the
| > >> | warnings to appear each time the workbook is opened.
| > >> |
| > >> | Thanks for any ideas you have.
| > >> |
| > >> | Kevin Lucas
| > >> |
| > >> |
| > >>
| > >>
| > >
| > >
| > >
| >
| >
|
|

 

[Kevin Lucas]

Excellent. Thanks to both of you for your help.
Kevin