How to identify a cell without a formula
 

I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I need
to visually see which cells are not based on a formula. I don't want to set
protection on.

One way is to setup your own UDF, like so

Function IsFormula(rng As Range) As Boolean
If rng.Count 1 Then
IsFormula = CVErr(xlErrValue)
Else
IsFormula = rng.HasFormula
End If
End Function

test like so

=IsFormula(A1)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kevin" wrote in message
...
I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater
than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from
the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I
need
to visually see which cells are not based on a formula. I don't want to
set
protection on.

What's wrong with simply doing:

<F5, <Special

Click "Formulas", Then <OK
?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Kevin" wrote in message
...
I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I need
to visually see which cells are not based on a formula. I don't want to set
protection on.

"Without a formula", wouldn't it be constants as opposed to formulas in step
3

--
Regards,

Peo Sjoblom


"RagDyeR" wrote in message
...
What's wrong with simply doing:

<F5, <Special

Click "Formulas", Then <OK
?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Kevin" wrote in message
...
I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater
than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from
the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I
need
to visually see which cells are not based on a formula. I don't want to
set
protection on.

Either way shows what he's looking for, but to be exact, I guess you're more
correct then I am.<g
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
"Without a formula", wouldn't it be constants as opposed to formulas in step
3

--
Regards,

Peo Sjoblom


"RagDyeR" wrote in message
...
What's wrong with simply doing:

<F5, <Special

Click "Formulas", Then <OK
?
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Kevin" wrote in message
...
I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater
than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from
the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I
need
to visually see which cells are not based on a formula. I don't want to
set
protection on.

with ISFORMULA in the same workbook, which is a requirement
of Conditional formatting. the C.F. Formula 1 might be
=AND(A10,ISNUMBER(A1),ISFORMULA(A1)=False)

The requirement of greater than 0 eliminates ragdyer's suggestion.

"Bob Phillips" wrote in message ...
One way is to setup your own UDF, like so

Function IsFormula(rng As Range) As Boolean
If rng.Count 1 Then
IsFormula = CVErr(xlErrValue)
Else
IsFormula = rng.HasFormula
End If
End Function

test like so

=IsFormula(A1)


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kevin" wrote in message
...
I have a worksheet set with = reference to a cell in another worksheet.
Conditional formatting is set to color the cell if the value is greater
than
0.
Is there a way to add to the conditional formating to the same cell that
will color the cell a different color if the value is input and not from
the
formula?

My goal is to identify cells that have had their formula overwritten with
manual input. There are times a manual input needs to be entered and I
need
to visually see which cells are not based on a formula. I don't want to
set
protection on.