Excel 4.0 Macro replacement OR hide warning

[joeycatanese]

Hello All,

I have a workbook with several sheets that have a mixture of formulas (that pull information from another sheet) and manual overwrites. I want to differentiate the cells that have formulas in them and those that are overwritten or user entered values.

Currently I am using Conditional Formatting and a Excel 4.0 Macro as follows:

Insert > Name > Define:
'Name in workbook': CellHasFormula
"Refers to':

=GET.CELL(48,INDIRECT ("rc",FALSE))

Then in the conditional formatting dialog I simply set the formula to:

=CellHasFormula

and apply it to all the cells I need it to.

It is simple and works wonderfully, the only problem is I get a dialog upon opening that states:
"This workbook contains one or more Microsoft Excel 4.0 macros. These macros may contain viruses or other harmful code. Open this workbook only if you trust the source."

I would like to get around this warning by one of two ways:
1) Hide this warning. (NOTE: This workbook is shared many different people over different locations so changing security settings in each version of excel is not really an option)
2) Replace the Excel 4.0 macro

Any suggestions?
Thanks in advance!
jc

 

[Andrew Poulsom]

You could use this UDF:

Function HasFormula(Cell As Range) As Boolean
    HasFormula = Cell.HasFormula
End Function

but your users would have to enable macros.

 

[joeycatanese]

Andrew,

Thanks for the quick reply. We already have the enable macros dialog box so that is not a problem, I just wanted to limit it down to one dialog box. But is there something I can put into the function to have it point to the current cell. For Example:

=HasFormula(Cell.Current)

as opposed to hard-coding:

=HasFormula(A1)
=HasFormula(A2)

I know I could to a Paste Special > Formatting which will carry the Conditional Formatting with it, but don't want to overwrite the current cell formatting (We have about 10 different background colors denoting different types are parts spread across several worksheets). I would like to avoid that if all possible.

Thanks
jc

 

[Andrew Poulsom]

What's the problem with entering a cell reference as an argument? If it's relative you can copy it elsewhere.

 

[joeycatanese]

The problem with entering it as a cell reference is that I am retrofitting this to an existing worksheet with many different formats. I would like to select the entire range and apply a single Conditional Format as I did with the 4.0 Macro. I just do not know how to reference a cell to itself.

Does this make sense?

 

[Andrew Poulsom]

If you select eg A1:D100 and enter the formula:

=HasFormula(A1)

the cell reference will adjust relatively applied to the rest of the range.

 

[joeycatanese]

Ah I see what you mean now. I feel kinda stupid now! Worked great! Thank you very much!