# Conditional Formatting, cell does not contain formula.



## wkirkcrawford

Greetings,

I'm trying to use Conditional Formatting to flag a cell that does not have a formula in it.  It has a number.

Any ideas?

W. Kirk Crawford
Tularosa, New Mexico


----------



## AlphaFrog

Conditional Formatting works the same on cells with constants as with formula cells.  Whats your question or issue? What condition(s) do you want to define?


----------



## wkirkcrawford

AlpahaFrog,

All the cells in one column has a formula.  There are times I change what is displayed to a number.

How to I flag those cells that have a number as opposed to a formula?

W. Kirk Crawford
Tularosa, New Mexico


----------



## jeffreybrown

See if this post helps.

post #7


----------



## AlphaFrog

*Conditional Formatting for Constants*

*http://www.mrexcel.com/forum/excel-questions/572929-conditional-format-if-cell-=-constant.html*
*
Excel 2007 : Conditional format for constants vs formulas*
*
Conditional Formatting of Hard Coded Cells (Constants) | FIG Consulting Partners*


----------



## wkirkcrawford

AlphaFrog,

I am using a Surface RT and it has Microsoft Office on it.  I can not use VBA.  I also look at the different links you gave me, but I am unable to find, >Insert>Name>Define.

Beside, I'm not sure where, cell or column, this is to be put in.

W. Kirk Crawford
Tularosa, New Mexico


----------



## AlphaFrog

You can find Excel guides and resources by simply doing a web search for something like...
_Excel Insert Name Define_







Click on "Define Name" and create the "Name" as instructed here (this is not VBA)...
*Conditional Formatting for Constants in Excel - AMT Financial Training*

Select the range of cells (it can be a large area) you want to conditionally format  to test for constants, and create the rule as instructed in the link.


----------



## wkirkcrawford

AlphaFrog,

Well it wouldn't take, _=NOT(GET.CELL(48,INDIRECT(“rc”,FALSE)))_

In my 'Refers to:' is ='2015-Jan 14'!$U$8:$U$135.  If I try to put that formula in there, =NOT(GET.CELL(48,INDIRECT(“rc”,FALSE))) I get and error message, "You've entered too many arguments for this function.  

I'm really confused.

W. Kirk Crawford
Tularosa, New Mexico


----------



## AlphaFrog

Try this...
*=NOT(GET.CELL(48,INDIRECT("rc",FALSE)))*


The the link used a type of quotation mark that is not compatible with your system...
_=NOT(GET.CELL(48,INDIRECT(“rc”,FALSE)))_


----------



## wkirkcrawford

AlphaFrog,

Great, it work.  No error messages at all.

But the Conditional Formatting didn't work.

I only have 6 choices.
>Format all cells based on their values
>Format only cells that contain
>Format only top or bottom ranked values
>Format only values that are above or below average
>Format only unique or duplicate values
>Use a formula to determine which cells to format

I used the last one.  And it didn't work.

It seams like in the "Range" is talking about the range of cells that I'm giving this name to.

Now I'm still confused.

Thank you for all you have put up with me.
W. Kirk Crawford
Tularosa, New Mexico


----------



## wkirkcrawford

Greetings,

I'm trying to use Conditional Formatting to flag a cell that does not have a formula in it.  It has a number.

Any ideas?

W. Kirk Crawford
Tularosa, New Mexico


----------



## AlphaFrog

I believe you're over thinking it.

I'm assuming you've successfully defined a "Name" called _*cellhasnoformula*_ using this 
*=NOT(GET.CELL(48,INDIRECT("rc",FALSE)))*

Conditional Formatting:

select your range before selecting conditional format (CF) menu item
Select CF 
_Use a formula to determine which cells to format_
Paste this formula *=cellhasnoformula*
 click _Format _and configure a color
OK


----------



## wkirkcrawford

AlphaFrog,

I tried that, except on mine, ="cellhasnoformula" in it.  But it still doesn't work.  I did a cut and paste so I wouldn't miss spell it.


W. Kirk Crawford
Tularosa, New Mexico


----------



## AlphaFrog

wkirkcrawford said:


> AlphaFrog,
> 
> I tried that, except on mine, ="cellhasnoformula" in it.  But it still doesn't work.  I did a cut and paste so I wouldn't miss spell it.
> 
> 
> W. Kirk Crawford
> Tularosa, New Mexico



No quotes. 
=cellhasnoformula
Why did you add quotes?

Did you configure the CF format? If yes, what did you format?

Is the defined "Name" exactly this _cellhasnoformula_ ?


----------



## wkirkcrawford

AlphaFrog,

I didn't add the quotes, it was there when I checked.  Beside, I removed the quotes and still nothing.

If I'm not mistaking, it flagged the Named Range.  All of the cells.

W. Kirk Crawford
Tularosa, New Mexico


----------



## wkirkcrawford

Greetings,

I've found a solution to my question.  (When the cell contains a number instead of a formula.)

Using Conditional Formatting, Format cell value that is greater than, the formula of that is in that cell.  Thus, if it is a number, it will be greater than the VLookUp that I'm searching.  When I put in a number, it will always be greater than the number VLookUp gives.  I'm allowed to give myself more units if I feel I might need it.

Thanks to everyone who has looked at this.

W. Kirk Cawford
Tularosa, New Mexico


----------



## HeinrichPaul

I just used under the conditional formatting =Not(isformula(a1)) no name needed.


----------

