Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different color
or something? Thanks, Pam

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

1. this will allow you to track who has made changes to a spreadsheet:
Tools-Track Changes-Highlight Changes

2. this will make Excel show the formulas instead of the values. It will not
be able to tell you that the formula was overwritten with a value, but I'm
assuming you know which cells should have a formula and only want to be able
to validate that the formulas are still there.
Tools-Options, in the View tab, check "Formulas"

Or 3. you can use this code to highlight cells that have formulas:

Public Sub HighlightFormulaCells()
Dim c As Range
For Each c In Sheet1.UsedRange
If c.HasFormula Then
c.Interior.Color = vbRed
End If
Next c
End Sub



"pmanoloff" wrote:

Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different color
or something? Thanks, Pam

Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is there
something else I should do? I'm not well versed in Excel macros--I'm an old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

P ut a break point in th eVBA window on the first line and then step through
the code.

On the first with statement highligh and press F9 - It should turn yellow.

return to your workbook sheet and perform a change to acrtivate macro.

the press F8 to sttep through code.

"pmanoloff" wrote:

Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is there
something else I should do? I'm not well versed in Excel macros--I'm an old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

Evidently I need the "For Dummies" version. I'm not quite getting it.

"Joel" wrote:

P ut a break point in th eVBA window on the first line and then step through
the code.

On the first with statement highligh and press F9 - It should turn yellow.

return to your workbook sheet and perform a change to acrtivate macro.

the press F8 to sttep through code.

"pmanoloff" wrote:

Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is there
something else I should do? I'm not well versed in Excel macros--I'm an old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

a break point in the code will stop the code from executing so we can ffind
the problem. right now we can't tell if the worksheet_change function is
being executed.

simply go into the VBA editor and highlight the line that says "With target"
and press F9 (the line should turn red). Go back into your worksheet and
make a change. If the the break point was reached the line in the VBA editor
will turn yellow.

Press F8 stepping through the code and write down which statement turned
yellow as you press F8.


"pmanoloff" wrote:

Evidently I need the "For Dummies" version. I'm not quite getting it.

"Joel" wrote:

P ut a break point in th eVBA window on the first line and then step through
the code.

On the first with statement highligh and press F9 - It should turn yellow.

return to your workbook sheet and perform a change to acrtivate macro.

the press F8 to sttep through code.

"pmanoloff" wrote:

Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is there
something else I should do? I'm not well versed in Excel macros--I'm an old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is
there
something else I should do? I'm not well versed in Excel macros--I'm an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed
over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

OK, I highlighted With target and pressed F9 and it turned red. I made a
change, and nothing happened. I pressed F8. (nothing happened). I cleared
that red line by pressing F9 again, and hilighted the next line (assuming
this is what put in the break), went back, made a change, nothing, f8, f9 on
that line to remove the red, and then the next line. All the way to the
bottom each line stayed red.

"Joel" wrote:

a break point in the code will stop the code from executing so we can ffind
the problem. right now we can't tell if the worksheet_change function is
being executed.

simply go into the VBA editor and highlight the line that says "With target"
and press F9 (the line should turn red). Go back into your worksheet and
make a change. If the the break point was reached the line in the VBA editor
will turn yellow.

Press F8 stepping through the code and write down which statement turned
yellow as you press F8.


"pmanoloff" wrote:

Evidently I need the "For Dummies" version. I'm not quite getting it.

"Joel" wrote:

P ut a break point in th eVBA window on the first line and then step through
the code.

On the first with statement highligh and press F9 - It should turn yellow.

return to your workbook sheet and perform a change to acrtivate macro.

the press F8 to sttep through code.

"pmanoloff" wrote:

Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is there
something else I should do? I'm not well versed in Excel macros--I'm an old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

Yes, I right clicked on the excel icon next to the word FILE so it is stored
in the project window under Microsoft Excel Objects, and is the last item
after my worksheets and it says ThisWorkbook. Do I need to name it and
execute it? I was assuming that since it was event code that it
automatically executed when I opened the workbook.

"Bob Phillips" wrote:

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is
there
something else I should do? I'm not well versed in Excel macros--I'm an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was keyed
over
with hard text? If not, is there a way to color the boxes a different
color
or something? Thanks, Pam

Sorry mate, I gave you the wrong instructions ... my bad.

This is what I should have said.

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Yes, I right clicked on the excel icon next to the word FILE so it is
stored
in the project window under Microsoft Excel Objects, and is the last item
after my worksheets and it says ThisWorkbook. Do I need to name it and
execute it? I was assuming that since it was event code that it
automatically executed when I opened the workbook.

"Bob Phillips" wrote:

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is
there
something else I should do? I'm not well versed in Excel macros--I'm
an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour
the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was
keyed
over
with hard text? If not, is there a way to color the boxes a
different
color
or something? Thanks, Pam

Bob, you are a genius! It works great! Two questions from the result - #1)
if I want to change the color, I assume I just change the number 38 and #2),
once I executed that, the undo button disappeared--any way to keep that?

"Bob Phillips" wrote:

Sorry mate, I gave you the wrong instructions ... my bad.

This is what I should have said.

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"pmanoloff" wrote in message
...
Yes, I right clicked on the excel icon next to the word FILE so it is
stored
in the project window under Microsoft Excel Objects, and is the last item
after my worksheets and it says ThisWorkbook. Do I need to name it and
execute it? I was assuming that since it was event code that it
automatically executed when I opened the workbook.

"Bob Phillips" wrote:

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen for
ThisWorkbook, and saved the file, tested it and it does not work. Is
there
something else I should do? I'm not well versed in Excel macros--I'm
an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written, colour
the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was
keyed
over
with hard text? If not, is there a way to color the boxes a
different
color
or something? Thanks, Pam

Yes
No
--
regards,
Tom Ogilvy

"pmanoloff" wrote in message
...
Bob, you are a genius! It works great! Two questions from the result -
#1)
if I want to change the color, I assume I just change the number 38 and
#2),
once I executed that, the undo button disappeared--any way to keep that?

"Bob Phillips" wrote:

Sorry mate, I gave you the wrong instructions ... my bad.

This is what I should have said.

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Yes, I right clicked on the excel icon next to the word FILE so it is
stored
in the project window under Microsoft Excel Objects, and is the last
item
after my worksheets and it says ThisWorkbook. Do I need to name it
and
execute it? I was assuming that since it was event code that it
automatically executed when I opened the workbook.

"Bob Phillips" wrote:

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen
for
ThisWorkbook, and saved the file, tested it and it does not work.
Is
there
something else I should do? I'm not well versed in Excel
macros--I'm
an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written,
colour
the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was
keyed
over
with hard text? If not, is there a way to color the boxes a
different
color
or something? Thanks, Pam

I have one more question on this macro. Can it be adapted to run on command
and highlight all cells that have been over-ridden rather than as each cell
is entered into?

"Tom Ogilvy" wrote:

Yes
No
--
regards,
Tom Ogilvy

"pmanoloff" wrote in message
...
Bob, you are a genius! It works great! Two questions from the result -
#1)
if I want to change the color, I assume I just change the number 38 and
#2),
once I executed that, the undo button disappeared--any way to keep that?

"Bob Phillips" wrote:

Sorry mate, I gave you the wrong instructions ... my bad.

This is what I should have said.

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Yes, I right clicked on the excel icon next to the word FILE so it is
stored
in the project window under Microsoft Excel Objects, and is the last
item
after my worksheets and it says ThisWorkbook. Do I need to name it
and
execute it? I was assuming that since it was event code that it
automatically executed when I opened the workbook.

"Bob Phillips" wrote:

Did you follow my instructions as to where to store that code?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"pmanoloff" wrote in message
...
Thanks for the quick response. I pasted the code in the VB screen
for
ThisWorkbook, and saved the file, tested it and it does not work.
Is
there
something else I should do? I'm not well versed in Excel
macros--I'm
an
old
Lotus baby. I did have a .net class a while back though.

"Bob Phillips" wrote:

This code will trap changes, and if a formula is over-written,
colour
the
cell

Option Explicit

Private fWasFormula As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Count = 1 Then
If fWasFormula Then
If Not Target.HasFormula Then
.Interior.ColorIndex = 38
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
fWasFormula = Target.HasFormula
End Sub




'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"pmanoloff" wrote in message
...
Does anyone know of a way to identify cells where a formula was
keyed
over
with hard text? If not, is there a way to color the boxes a
different
color
or something? Thanks, Pam