Change Cell Background color when Cell changes

[Steve Roberts]

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

[Spreadsheet Solutions]

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: contact@rosenkrantz.nl
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" <Stever@Discussiongroups.com> wrote in message
news:u70QhIrPFHA.2680@TK2MSFTNGP09.phx.gbl...
>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 Roberts]

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" <contact@rosenkrantz.nl> wrote in message
news:d3ecid$s50$1@reader08.wxs.nl...
> 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: contact@rosenkrantz.nl
> W: www.rosenkrantz.nl
> ------------------------
>
>
>
>
> "Steve Roberts" <Stever@Discussiongroups.com> wrote in message
> news:u70QhIrPFHA.2680@TK2MSFTNGP09.phx.gbl...
>>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
>>
>
>

[Spreadsheet Solutions]

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: contact@rosenkrantz.nl
W: www.rosenkrantz.nl
------------------------




"Steve Roberts" <Stever@Discussiongroups.com> wrote in message
news:eHgWOPsPFHA.1096@TK2MSFTNGP12.phx.gbl...
> 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" <contact@rosenkrantz.nl> wrote in message
> news:d3ecid$s50$1@reader08.wxs.nl...
>> 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: contact@rosenkrantz.nl
>> W: www.rosenkrantz.nl
>> ------------------------
>>
>>
>>
>>
>> "Steve Roberts" <Stever@Discussiongroups.com> wrote in message
>> news:u70QhIrPFHA.2680@TK2MSFTNGP09.phx.gbl...
>>>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
>>>
>>
>>
>
>

[Rowan]

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
>
>
>

[rvExcelNewTip]

1. Rowan's approach is aguably the simplest. It doesn't even require an additional button to clobber your worksheet as the changes are captured automatically by Excel's events handlers.
However, in my opinion it has to be complemented, because upon closing the workbook, the changed cells will remain colored and so they will stay upon subsequent re-opening of the workbook. Eventually, the whole worksheet will turn blue...
Therefore, it may be a good idea to reset the colors before saving the workbook. This can be accomplished by adding following code to the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.FindFormat.Interior.ColorIndex = 37
.ReplaceFormat.Interior.ColorIndex = xlColorIndexNone
End With

Worksheets("SheetName").UsedRange.Replace What:="", replacement:="", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=True, ReplaceFormat:=True

End Sub

2. What is a Change? The drawback of using the Worksheet_Change event is that when a cell that contains, let's say 123 is replaced by 123 (intentionally the same values) the event is triggered. And I know of no way to capture the old value in that event. If this is an unbearable burden then I'm afraid you have to return to Mark's suggestion and some (!) additional coding;

[Steve Roberts]

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" <Rowan@discussions.microsoft.com> wrote in message
news:1950ABBA-363E-4171-94CB-6FB2B562DD14@microsoft.com...
> 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
>>
>>
>>

[rvExcelNewTip]

You can obtain the desired effect without disabling the event as follows:

1. Put a command button on your worksheet and call it cmdColor; Its caption is left to your imagination.

2. Add a standard module to your workbook. When its code pane opens up, put the following code line into it:

Public bColorSwitch As Boolean

3. Switch to the program code of the Worksheet and add following subs:

Private Sub cmdColor_Click()

bColorSwitch = Not bColorSwitch

Select Case bColorSwitch
Case True
cmdColor.BackColor = RGB(153, 204, 255) 'color37
Case False
cmdColor.BackColor = &H8000000F 'button color
End Select

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case bColorSwitch
Case True
Target.Interior.ColorIndex = 37
Target.Interior.Pattern = xlSolid
Case False
End Select

End Sub

4. For completeness, reset the button color when closing the workbook. Add following code at the Thisworkbook level:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Application
.FindFormat.Interior.ColorIndex = 37
.ReplaceFormat.Interior.ColorIndex = xlColorIndexNone
End With

Worksheets("SheetName").UsedRange.Replace What:="", replacement:="", lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False, searchformat:=True, ReplaceFormat:=True

'Reset to Color switch and button color
bColorSwitch = False
Worksheets("SheetName").cmdColor.Background = &H8000000F

End Sub

5. Test the code: Clicking the command button will change its color. This is a visual indication that from now on any changes to a cell will change its color. Click the button again: the original button background appears and cell changes will not change cell colors.

[Rowan]

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" <Rowan@discussions.microsoft.com> wrote in message
> news:1950ABBA-363E-4171-94CB-6FB2B562DD14@microsoft.com...
> > 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
> >>
> >>
> >>
>
>
>

[Doug Glancy]

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" <Rowan@discussions.microsoft.com> wrote in message
news:B27432A5-ABE0-4BA0-903D-43E29365C044@microsoft.com...
> 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" <Rowan@discussions.microsoft.com> wrote in message
> > news:1950ABBA-363E-4171-94CB-6FB2B562DD14@microsoft.com...
> > > 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
> > >>
> > >>
> > >>
> >
> >
> >

[Rowan]

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" <Rowan@discussions.microsoft.com> wrote in message
> news:B27432A5-ABE0-4BA0-903D-43E29365C044@microsoft.com...
> > 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" <Rowan@discussions.microsoft.com> wrote in message
> > > news:1950ABBA-363E-4171-94CB-6FB2B562DD14@microsoft.com...
> > > > 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 Roberts]

Thanks for your help guys. This was a very simple solution (after you
pointed me in the right direction :-)

This is what I came up with. I created a public boolean variable and set the
default to false. When I click a button on the form it changes the variable
to true. The Worksheet_Change event looks at the variable to decide if it
should run or not.

'********** Public variable if true enables tracking changes.
Public blTrackChanges As Boolean

'*********************************************************
Private Sub Worksheet_Activate()

'************** Turn Off Modifications by default
blTrackChanges = False

End Sub

'**********************************************************
Private Sub btnHighlightModifications_Click()

'********** Enable Highlight Modifications
blTrackChanges = True

End Sub

'**********************************************************
Private Sub Worksheet_Change(ByVal Target As Range)

If blTrackChanges = True Then 'Check Public Variable blTrackChanges
If true then enable Highlight Modifications
If Target.Text = "" Then GoTo Bypass

If MsgBox("Do You Want To HighLight This Modification?", vbOKCancel,
"Highlight Modifications?") = vbOK Then
Target.Interior.ColorIndex = 45 'Change the background of
each changed field to Orange
Target.Interior.Pattern = xlSolid 'Change the background fill
pattern to Solid
Else
GoTo Bypass
End If
End If

Bypass:

End Sub




"Rowan" <Rowan@discussions.microsoft.com> wrote in message
news:136CBB6A-CA37-48E1-A8F4-083A55BB517C@microsoft.com...
> 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" <Rowan@discussions.microsoft.com> wrote in message
>> news:B27432A5-ABE0-4BA0-903D-43E29365C044@microsoft.com...
>> > 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" <Rowan@discussions.microsoft.com> wrote in message
>> > > news:1950ABBA-363E-4171-94CB-6FB2B562DD14@microsoft.com...
>> > > > 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
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >
>>
>>
>>