I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the background
of that cell a different color. Is there a way to do this?

Thanks

Steve

Steve;

Unless you are familiar with writing some VBA macro you don't have many
options.
I could suggest one, but that is with one thing in mind.

Is it so that you could be satisfied with the fact that a cell change is a
change in value, say below zero or something.
If so, you could work with conditional formatting.
Lots of examples about that can be found with google or on the forum.

If not, you have to work with VBA macro's and opt for an application event
called Worksheet_SelectionChange.
Chip Pearson has examples on his website, but this is not easy.

Mark.
--
Rosenkrantz Spreadsheet Solutions
Witkopeend 24
1423 SN
Netherlands
------------------------
E:
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" wrote in message
...
I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the background
of that cell a different color. Is there a way to do this?

Thanks

Steve

Mark,

Thanks for the quick reply.

I am familiar with vba in Access but not so much in Excel. Is there a way
after all the information has been entered in a sheet to test each cell to
see if the information has changed? Most of the changes would be text not
numbers. If I can test each cell for changes I can then change the
background colors easy enough.

Steve


"Spreadsheet Solutions" wrote in message
...
Steve;

Unless you are familiar with writing some VBA macro you don't have many
options.
I could suggest one, but that is with one thing in mind.

Is it so that you could be satisfied with the fact that a cell change is a
change in value, say below zero or something.
If so, you could work with conditional formatting.
Lots of examples about that can be found with google or on the forum.

If not, you have to work with VBA macro's and opt for an application event
called Worksheet_SelectionChange.
Chip Pearson has examples on his website, but this is not easy.

Mark.
--
Rosenkrantz Spreadsheet Solutions
Witkopeend 24
1423 SN
Netherlands
------------------------
E:
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" wrote in message
...
I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the background
of that cell a different color. Is there a way to do this?

Thanks

Steve

Steve;

Pfhhh, that's some question.
Sure there should be a way, but at the moment I can't think of a direct
solution with VBA.
The point or problem is that you have to work with two databases to test the
input where you have only one.

I would suggest a quick and dirty solution.
Work with a dummy worksheet and a variance-sheet.
Like in Budget - Actual - Variance.
I create these kind of finacial statements very often and the concpet could
be the same for you.

I don't know if the "database" you're working with is fixed speaking in
terms of rows and columns.
If so, create an exact copy and use the first sheet as the input sheet for
new data.
Sheet three is the matching sheet were you test the contents of the same
cells for the other two worksheets.
If the contents of a cell, is unequal, you can let the background color
change with conditional formatting.

Fact is that the colors are then on a third sheet and not on the original.
Next, you must keep in mind that when you must match again, you first have
to transport your old inputdata
to the dummysheet, so that you can match these data with the new inputdata.

Complicated ?
A little, but I think you'll manage.

Mark.
--
Rosenkrantz Spreadsheet Solutions
Witkopeend 24
1423 SN
Netherlands
------------------------
E:
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" wrote in message
...
Mark,

Thanks for the quick reply.

I am familiar with vba in Access but not so much in Excel. Is there a way
after all the information has been entered in a sheet to test each cell to
see if the information has changed? Most of the changes would be text not
numbers. If I can test each cell for changes I can then change the
background colors easy enough.

Steve


"Spreadsheet Solutions" wrote in message
...
Steve;

Unless you are familiar with writing some VBA macro you don't have many
options.
I could suggest one, but that is with one thing in mind.

Is it so that you could be satisfied with the fact that a cell change is
a change in value, say below zero or something.
If so, you could work with conditional formatting.
Lots of examples about that can be found with google or on the forum.

If not, you have to work with VBA macro's and opt for an application
event called Worksheet_SelectionChange.
Chip Pearson has examples on his website, but this is not easy.

Mark.
--
Rosenkrantz Spreadsheet Solutions
Witkopeend 24
1423 SN
Netherlands
------------------------
E:
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" wrote in message
...
I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the
background of that cell a different color. Is there a way to do this?

Thanks

Steve

Steve

Here is a simple solution that you can use as a starting point.
Right click the sheet tab and select view code.
Past this code onto the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub

This will change the background of any cell which is changed to blue. You
can play around with the ColorIndex to select another colour.

Hope this helps
Rowan

"Steve Roberts" wrote:

I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the background
of that cell a different color. Is there a way to do this?

Thanks

Steve

That's Awesome! Just what I needed!

Is there a way to disable the event when I don't want to have it change the
background color? Something like in normal mode the event works but after
clicking a button it disables it?



"Rowan" wrote in message
...
Steve

Here is a simple solution that you can use as a starting point.
Right click the sheet tab and select view code.
Past this code onto the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub

This will change the background of any cell which is changed to blue. You
can play around with the ColorIndex to select another colour.

Hope this helps
Rowan

"Steve Roberts" wrote:

I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the
background
of that cell a different color. Is there a way to do this?

Thanks

Steve

Steve

There isn't really any way to disable this completely. You have two options:
1. You can stop it working on certain cells. The range variable Target
represents the cell in which the change has taken place. So for example if
you only wanted the macro to work in Cell A5 you could do something like this
If Target.Range = "$A$5" then
'Do some stuff
End If

Or if you wanted it not to work on a certain column eg column B you could use:
If Target.Column < 2 Then
'Do whatever
End If

The second option if you want to disable the macro completey and restart it
with the click of a button would require programming to the VBE and actually
removing the procedure and then recreating it. You could have two buttons -
one which creates the procedure, and therefore enables it, and one which
deletes the procedure. This solution is more prone to error and requires the
users to have enabled Access to their VBA projects. For details on how you
would do this see the sections titled "Creating An Event Procedure" and
"Deleting A Procedure From A Module" in Chip Pearson's website:
www.cpearson.com/excel/vbe.htm.

If you go this route you'd probably want to add the line:
Application.VBE.MainWindow.Visible = False
to the end of the macro which creates the event proc so that the user is not
presented with the VBE window after the macro has run.

Hope this helps
Rowan

"Steve Roberts" wrote:

That's Awesome! Just what I needed!

Is there a way to disable the event when I don't want to have it change the
background color? Something like in normal mode the event works but after
clicking a button it disables it?



"Rowan" wrote in message
...
Steve

Here is a simple solution that you can use as a starting point.
Right click the sheet tab and select view code.
Past this code onto the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub

This will change the background of any cell which is changed to blue. You
can play around with the ColorIndex to select another colour.

Hope this helps
Rowan

"Steve Roberts" wrote:

I have a Department that wants to have a button added to a worksheet that
would 'watch' for any cell changes. If a cell changed, make the
background
of that cell a different color. Is there a way to do this?

Thanks

Steve

Steve and Rowan,

If I'm understanding correctly, you could turn this off and on. For
example, you could put a checkbox from the Controls Toolbar on the worksheet
and do the coloring depending on whether it's checked or not:

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.CheckBox1 = True Then
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End If
End Sub

hth,

Doug

"Rowan" wrote in message
...
Steve

There isn't really any way to disable this completely. You have two
options:
1. You can stop it working on certain cells. The range variable Target
represents the cell in which the change has taken place. So for example if
you only wanted the macro to work in Cell A5 you could do something like
this
If Target.Range = "$A$5" then
'Do some stuff
End If

Or if you wanted it not to work on a certain column eg column B you could
use:
If Target.Column < 2 Then
'Do whatever
End If

The second option if you want to disable the macro completey and restart
it
with the click of a button would require programming to the VBE and
actually
removing the procedure and then recreating it. You could have two
buttons -
one which creates the procedure, and therefore enables it, and one which
deletes the procedure. This solution is more prone to error and requires
the
users to have enabled Access to their VBA projects. For details on how you
would do this see the sections titled "Creating An Event Procedure" and
"Deleting A Procedure From A Module" in Chip Pearson's website:
www.cpearson.com/excel/vbe.htm.

If you go this route you'd probably want to add the line:
Application.VBE.MainWindow.Visible = False
to the end of the macro which creates the event proc so that the user is
not
presented with the VBE window after the macro has run.

Hope this helps
Rowan

"Steve Roberts" wrote:

That's Awesome! Just what I needed!

Is there a way to disable the event when I don't want to have it change
the
background color? Something like in normal mode the event works but
after
clicking a button it disables it?



"Rowan" wrote in message
...
Steve

Here is a simple solution that you can use as a starting point.
Right click the sheet tab and select view code.
Past this code onto the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub

This will change the background of any cell which is changed to blue.
You
can play around with the ColorIndex to select another colour.

Hope this helps
Rowan

"Steve Roberts" wrote:

I have a Department that wants to have a button added to a worksheet
that
would 'watch' for any cell changes. If a cell changed, make the
background
of that cell a different color. Is there a way to do this?

Thanks

Steve

That's a good suggestion - much simpler than programming to the VBE.

"Doug Glancy" wrote:

Steve and Rowan,

If I'm understanding correctly, you could turn this off and on. For
example, you could put a checkbox from the Controls Toolbar on the worksheet
and do the coloring depending on whether it's checked or not:

Private Sub Worksheet_Change(ByVal Target As Range)
If Me.CheckBox1 = True Then
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End If
End Sub

hth,

Doug

"Rowan" wrote in message
...
Steve

There isn't really any way to disable this completely. You have two
options:
1. You can stop it working on certain cells. The range variable Target
represents the cell in which the change has taken place. So for example if
you only wanted the macro to work in Cell A5 you could do something like
this
If Target.Range = "$A$5" then
'Do some stuff
End If

Or if you wanted it not to work on a certain column eg column B you could
use:
If Target.Column < 2 Then
'Do whatever
End If

The second option if you want to disable the macro completey and restart
it
with the click of a button would require programming to the VBE and
actually
removing the procedure and then recreating it. You could have two
buttons -
one which creates the procedure, and therefore enables it, and one which
deletes the procedure. This solution is more prone to error and requires
the
users to have enabled Access to their VBA projects. For details on how you
would do this see the sections titled "Creating An Event Procedure" and
"Deleting A Procedure From A Module" in Chip Pearson's website:
www.cpearson.com/excel/vbe.htm.

If you go this route you'd probably want to add the line:
Application.VBE.MainWindow.Visible = False
to the end of the macro which creates the event proc so that the user is
not
presented with the VBE window after the macro has run.

Hope this helps
Rowan

"Steve Roberts" wrote:

That's Awesome! Just what I needed!

Is there a way to disable the event when I don't want to have it change
the
background color? Something like in normal mode the event works but
after
clicking a button it disables it?



"Rowan" wrote in message
...
Steve

Here is a simple solution that you can use as a starting point.
Right click the sheet tab and select view code.
Past this code onto the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
End Sub

This will change the background of any cell which is changed to blue.
You
can play around with the ColorIndex to select another colour.

Hope this helps
Rowan

"Steve Roberts" wrote:

I have a Department that wants to have a button added to a worksheet
that
would 'watch' for any cell changes. If a cell changed, make the
background
of that cell a different color. Is there a way to do this?

Thanks

Steve