Format cells without a formula

D

durbat2

Hi

I have a large spreadsheet with formulae in multiple cells.
Occassionally under some circumstances I have to replace a formula
with manually entered data and I need to be able to automatically
highlight these cells as being different.
I have found out how to conditionally format a cell containing a
formula but can't make the adjsutment to format a cell that doesn't
contain a formula.
The code I found is:

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function

Is it as simple as changing "HasFormula" to "doesnothaveformula"?

Thanks
Martin
 
D

David Biddulph

The use of NOT sounds more likely.

You can either use =NOT(IsFormula(cellref)) as your CF condition, or change
your function to something like:
Function IsNotFormula(cell_ref As Range)
IsNotFormula = Not (cell_ref.HasFormula)
End Function
 
A

Ashish Mathur

Hi,

You may try this.

1. Press Ctrl+F3 to get the Define Name box;
2. Type in a name there, say cellhasformula
3. In the Refers to box, type =GET.CELL(48,INDIRECT("rc",FALSE))
4. Now go to the first number of the range and in the "Formula Is" drop down
box, type the following formula =cellhasformula=FALSE
5. Select the desired formatting
6. Now copy down the conditional formatting

You will now see all the hard coded numbers in the desired formatting.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top