Over keying a formula - Can I set this to automatically bold ?

[Guest]

Hi All,

Hopefully this is a nice easy one that somebody can help me out with

I have a column of data with formula's in (forecasted sales) during the
month I over key these when the actual orders come in. Is there a way that I
can set something up so that when I over key the formula the text changes in
some way either a different colour or bold for example. I have tried using
conditional formatting but don't seem to be getting anywhere.

Thanks in advance for your help

Regards

Dave

 

[Bernie Deitrick]

Dave,

You need to use an event.

Copy the code below, right-click your sheet tab, select "View Code" and paste the code into the
window that appears.

This code will make cells with formulas that have been over-written bold and red.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewVal As Variant
With Application
.EnableEvents = False
NewVal = Target.Value
.Undo
If Target.HasFormula Then
Target.Font.Bold = True
Target.Font.ColorIndex = 3
End If
Target.Value = NewVal
.EnableEvents = True
End With
End Sub

 

[Gord Dibben]

You can add this User Defined Function to your workbook.

Function IsFormula(Cell)
Application.Volatile
IsFormula = Cell.HasFormula
End Function

Select the range to Format then

Format>Conditional Formatting>Formula is: =isformula($A1)

I assumed the range was in column A.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks for that Bernie,

It does exactly what I want, a couple of things though.......
It appears to have disabled my undo button, I can't seem to undo any of my
often keying errors and if I try to recopy the formula back in to the cell
after I have over keyed, it doesn't put the formula back in only the value of
what the formula would be. I've tried taking the event back out and things go
back to normal

Any suggestions?

Thanks again

Dave

 

[Bernie Deitrick]

Dave,

Sorry, but running event code will dump Excel's memory stack, prevent Undo.

Try Gord's solution of using the UDF in the Cond. Formatting.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie,

How do I add a user defined function? Do I just copy the code into the view
code window as per your suggestion ?

Regards

Dave

 

[Bernie Deitrick]

Dave,

No. You need to put it into a standard codemodule. See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

for more detailed instructions.

HTH,
Bernie
MS Excel MVP