how to change cell colour by simply clicking on it

[lovebunny]

I am taking a computer course and needed to create an activity using
Excel. I put together an activity where Kindergarten students graph
M&M candies according to colour. What I would like to do is allow for
cells to change colour when a child clicks on it with the mouse. (e.g.
for each yellow M&M, the child is to click on cell in graph under the
pic of the yellow M&M. These cells would in turn change to
corresponding colour.)

I would also need to know how to have cell return to original colour
when clicked on twice- in case a mistake is made.

I have an example of what I want to do- see here:
http://www.scs.sk.ca/cyber/elem/lea.../mathk5interact/dataman/smarties/rec_sht.html

Thank you in advance for your help- I am really new at this and
unfortunately can't figure this out on my own.

Jovette

 

[Bob Phillips]

You could try this

put some autoshapes in C3, E3 and G3 as an example and fill colour them
yellow, red, and blue. Name them MMin3, MMin5, MMin7 and then add this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "C4:C20,E4:E20,G4:G20"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Interior.ColorIndex = xlColorIndexNone Then
.Interior.ColorIndex = Me.Shapes("MMin" &
..Column).Fill.ForeColor.SchemeColor - 7
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


click a cell in columns C, E or G and see what happens.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[lovebunny]

Hi Bob-

Thanks for your help with this.

Unfortunately, I'm not really sure where to put this info. Is there a
way that I can attach my doc so that you can see what I have put
together so far and guide me from there? I am using Excell 2000.

Thanks,
Jovette

 

[Bob Phillips]

Take a look at this file, and see what you can understand, post back with
any questions

http://cjoint.com/?ikqUfLyAe0

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[lovebunny]

Okay, that helped!

I created autoshapes and I pasted them behind my candy images (I had
previously found clipart of different coloured M&M candy and pasted
them in each column). I gave them a corrsponding colour and named them
MMin1, MMin2, MMin3, MMin4, MMin5 and MMin6 (I went to "insert",
"name", then "define" to do this... Is this correct?) I figured out
how to go in the code part. I pasted the formula you gave me but
modified it according to where my autoshapes are. This is what I
typed:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String =
"D1020,E10:E20,F10:F20,G10:G20,H10:H20,I10:I20,"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Interior.ColorIndex = xlColorIndexNone Then
.Interior.ColorIndex = Me.Shapes("MMin" &
..Column).Fill.ForeColor.SchemeColor - 7
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


Now, the cells that I click into change to white. When I click twice,
they say white. What part of the above formula must I change to get
the cells to be the right colours when I click on them once? My colours
are: D column - yellow, E- brown, F- blue, G-green, H-orange, I-red.
Another question:
Is there also a way that I can clear the graph once the students are
done their activity so that they can start over again?

Thanks again for your help!!!

Jovette

 

[Bob Phillips]

The way to rename the shapes is to type the new name into the Names box, not
via the menu. They should also be named in line with the target columns,
that is MMin4, MMin 5, etc.. not 1,2,3,...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[lovebunny]

IT WORKED!!!
Thanks SO very much, Bob! I could have never done this without your
help!!!! I really appreciate the time you've given me.

One last question-
How can I reset the graph and empty the coloured cells quickly so that
another student can use the graph?

Jovette

 

[Bob Phillips]

Just create a simple macro

Sub ClearCells()
Dim cell As Range

For each cell in
Range("D1020,E10:E20,F10:F20,G10:G20,H10:H20,I10:I20")
cell.Interior.Colorindex = xlColorindexNone
Next cell

End Sub

maybe add a button to the worksheet from the forms toolbar, and assign that
macro to the button.

BTW, that is an unusual name, is it French-Canadian?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[lovebunny]

Don't worry- I figured out that I only need to click and drag over the
graph area to clear the cells.
Thanls again for all your help. I am forever grateful!
Jovette

 

[lovebunny]

OK, I'll try that- I just noticed that I was deleting the colours from
the other cells as well as the ones on the graph.

OK- I tried it and it worked!!!! My first macro!

Regarding my name-
Yeah- it's very French-Canadian! I guess it's a name that grows on ya
after a while...

Thanks again, Bob! You're awesome!!
Jovette