Conditional Formatting when Overtyyping a Function

  • Thread starter Thread starter H
  • Start date Start date
H

H

Is there a way to use conditional formatting to shade a cell background if a
user overtypes a formula that already exists in a cell ?

In case I'm not being clear, I have to enter a figure in A1, calculation
results are then shown in B1 and C1. However if those calculations do not
match other information already given to me then I am manually overtyping
the field and then clicking on the fill colour icon to notify myself that it
is an overwrite. Can this be done automatically ? if not with Conditional
formatting then could anyone point me in the direction of a UDF for this
sort of thing.

Many thanks

Harry
 
-----Original Message-----
Is there a way to use conditional formatting to shade a cell background if a
user overtypes a formula that already exists in a cell ?

In case I'm not being clear, I have to enter a figure in A1, calculation
results are then shown in B1 and C1. However if those calculations do not
match other information already given to me then I am manually overtyping
the field and then clicking on the fill colour icon to notify myself that it
is an overwrite. Can this be done automatically ? if not with Conditional
formatting then could anyone point me in the direction of a UDF for this
sort of thing.

Many thanks

Harry

Hello Harry
Click to expand...

What i have done in the past is "nest" the conditions. Use
the add button to add more conditions. by doing this I
have three tests with three possible outputs , True ,
False , Other with Other being no macth at all. Not sure
if it fits your scenario.
 
Harry
I think I found a way to do this. My example has numbers in A1 and B1 with the formula =A1*B1 in cell C1

The condition is "Formula is" =A1*B1<>C

Replace A1*B1 with your formula, and C1 with the reference of the cell containing your formula. This way if the calculated value of the formula does not equal the value in the cell, the conditional format will be applied

Good Luck
Mark Graesse
(e-mail address removed)


----- H wrote: ----

Is there a way to use conditional formatting to shade a cell background if
user overtypes a formula that already exists in a cell

In case I'm not being clear, I have to enter a figure in A1, calculatio
results are then shown in B1 and C1. However if those calculations do no
match other information already given to me then I am manually overtypin
the field and then clicking on the fill colour icon to notify myself that i
is an overwrite. Can this be done automatically ? if not with Conditiona
formatting then could anyone point me in the direction of a UDF for thi
sort of thing

Many thank

Harr
 
Many thanks to Mark and Jim

Mark's solution was what i was looking for. This is a great time saver for
me as I have to make several hundred of these changes in a very large
workbook.

Thanks again

Harry


Mark Graesser said:
Harry,
I think I found a way to do this. My example has numbers in A1 and B1
Click to expand...
with the formula =A1*B1 in cell C1.
The condition is "Formula is" =A1*B1<>C1

Replace A1*B1 with your formula, and C1 with the reference of the cell
Click to expand...
containing your formula. This way if the calculated value of the formula
does not equal the value in the cell, the conditional format will be
applied.
 
Consider this:
Check with conditional format if B1 equals the formula using the input
in A1. If not, it means that you overwited the formula, than apply the
shading. Same with C1.
Ilan
 
Back
Top