Hi Joseph,

I think you have answered your own question. Can you simply add a
CheckBox control to the worksheet (assume added at design time and named
"CheckBox1".

Then you simply check the value in your Change event handler:


Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.OLEObjects("CheckBox1").Object.Value = True Then
Target.interior.colorindex=5
End If
End Sub

Is this what you want?

-TJ

On Mon, 17 May 2004 13:40:27 -0500, wrote:

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/