cell color change with click and record the the time and date

C

Cyndyoxox

How would I change the color of a block of cells with one click of the mouse
and then the next click a different color?

Example cells
on my excel 2003 page 7,8,9/DEF make a block. I need the first click in the
block to change the background green and record the Date/time somewhere else,
2nd click in the block the background will turn blue, time/date are recorded
( I will need them for later calculations) the 3rd could change it back to
white, make a cycle or RED and not change from red, to signal the other
time/date stamps are going to be inaccurate
 
G

Gary''s Student

In cell D7 insert a hyperlink to itself (don't use the hyperlink function).
Copy D7 and paste from D7 thru F9.

Click on any cell in the block and you will jump to D7

In the worksheet code area insert the following event macro:

Public clr As Variant
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Set r = Range("D7:F9")
If Target.SubAddress = "Sheet1!D7" Then
If IsEmpty(clr) Or clr = 5 Then
r.Interior.ColorIndex = 50
clr = 50
Else
r.Interior.ColorIndex = 5
clr = 5
End If
End If
End Sub

Clicking in the block will toggle back and forth between green and blue
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top