Add a control toolbox button on that sheet and use code something like

Option Explicit

Dim fChange As Boolean

Private Sub CommandButton1_Click()
fChange = Not fChange
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If fChange Then
'your code
'end if
End Sub

The button then acst as a switch turn the formatting on or off

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Joseph Donnelly " wrote in
message ...
I currently have an Excel worksheet with data on it. I would like to be
able to change/edit the value of a particular cell and have the cell
background turn from white to, say, blue. I am aware of the idea of
creating a Private sub Worksheet_Change(ByVal Target as Range)
routine and placing code in this program that will assign the desired
color to the cell in question. The problem is that if I create a sub
routine such as:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.interior.colorindex=5

End sub

..as soon as I open or select the worksheet the above code is already
in affect. If I change a value in a data cell it turns to the desired
color. I would like instead to have the sheet only honor the above
sub routine when I choose to use it. Perhaps by using a button linked
to this sub routine/macro I could click on.

The problem I am seeking an answer for is how, or can one, cause the
worksheet to at one moment in time turn/utilize this "change the color
code" on and at another time turn it off. Ultimately I would like to
bring data into a vacant worksheet and not have it turn to a different
color right away. After I hit a button, or some other means of
telling Excel it is time to cause cell backgrounds to change color,
then any change to a cell or range of cells would turn to the desired
color.

Any help you could give me on this subject or means to control when a
particular worksheet goes into change mode would be greatly
appreciated. Joseph Donnelly


---
Message posted from http://www.ExcelForum.com/