Excell and changing cell colour
 

I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with my
class of children which uses the mrand function from the morefunc add-in. In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible to
do this (the cells i want to change already have the mrand function in them)

Excell and changing cell colour
 

This code will set a colour on selecting a cell, and change it on
re-selection or double-click.

Option Explicit

Const WS_RANGE As String = "H1:H10" '<=== change to suit


Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As
Boolean)
SetColour target
End Sub

Private Sub Worksheet_SelectionChange(ByVal target As Range)
SetColour target
End Sub

Private Sub SetColour(ByVal target As Range)
Dim idx As Long
If Not Intersect(target, Me.Range(WS_RANGE)) Is Nothing Then
With target
idx = Int((56 * Rnd) + 1)
.Interior.ColorIndex = idx
End With
End If
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.



--
HTH

Bob Phillips

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

"sadsfan" wrote in message
...
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with
my
class of children which uses the mrand function from the morefunc add-in.
In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible
to
do this (the cells i want to change already have the mrand function in
them)

Excell and changing cell colour
 

Hi
This will make one click be yellow and double click green on sheet 1.
In the VBA editor double click sheet 1 in the Project Window. Paste in
the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Target.Interior.ColorIndex = 10
Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub

Note that the selectionChange only kicks in when you change cells, so
clicking the cell you are already on might not do anything.
You can also restrict the colour changes to a specific area on your
sheet, and not on others.
you can also make colour changes apply to all sheets in a workbook.
Post back if you need things refined a little.
regards
Paul

sadsfan wrote:
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with my
class of children which uses the mrand function from the morefunc add-in. In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible to
do this (the cells i want to change already have the mrand function in them)

Excell and changing cell colour
 

You would use the selectionchange and beforedoubleclick events to run code
that changes the color.

These would be found in the sheet module of the sheet.

Right click on the sheet tab and select view code. Then in the resulting
module, in the left dropdown at the top, select Worksheet. In the right
dropdown select SelectionChange. You will get an event declaration like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

put you code there

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If not Intersect(Target,Range("B2:Z26")) is nothing then
Target.Interior.ColorIndex = 5
End if
End Sub

Likewise for the before double click


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If not Intersect(Target,Range("B2:Z26")) is nothing then
Target.Interior.ColorIndex = 3
End if
Cancel = True
End Sub


To see the colors corresponding to ColorIndex you can run this macro on a
blank sheet

Sub ShowColors()
Dim i As Long
For i = 0 To 56
Cells(i + 1, 1).Value = i
Cells(i + 1, 2).Interior.ColorIndex = i
Next
End Sub

in the VBE (alt+F11) select Insert Module.

Put this code in that module (a general/standard module)

then go back to Excel (Alt+F11) and go to Tools=Macro=Macros and select
Showcolors, hit run


Chip Pearson has an overview of Events at his site:

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy




"sadsfan" wrote in message
...
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with
my
class of children which uses the mrand function from the morefunc add-in.
In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible
to
do this (the cells i want to change already have the mrand function in
them)

Excell and changing cell colour
 

Hi paul, your code workde fine except for the fact that I have a command
button in sheet 1 which is clicked to randomize the numbers, when I do this
the cells turn yellow (equal to 1 click of the mouse). I've tried pasting the
command button code elswher (in a module) but then it doesn't work. any ideas?

" wrote:

Hi
This will make one click be yellow and double click green on sheet 1.
In the VBA editor double click sheet 1 in the Project Window. Paste in
the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Target.Interior.ColorIndex = 10
Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub

Note that the selectionChange only kicks in when you change cells, so
clicking the cell you are already on might not do anything.
You can also restrict the colour changes to a specific area on your
sheet, and not on others.
you can also make colour changes apply to all sheets in a workbook.
Post back if you need things refined a little.
regards
Paul

sadsfan wrote:
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with my
class of children which uses the mrand function from the morefunc add-in. In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible to
do this (the cells i want to change already have the mrand function in them)

Excell and changing cell colour
 

Hi
In your command button code, try to remove any activates or selections
(which trigger the change event)

e.g you can replace
Range("A1").Select
Selection.Value = 3

with
Range("A1").Value = 3

Post your button code if you are not sure what to replace.
regards
Paul

sadsfan wrote:
Hi paul, your code workde fine except for the fact that I have a command
button in sheet 1 which is clicked to randomize the numbers, when I do this
the cells turn yellow (equal to 1 click of the mouse). I've tried pasting the
command button code elswher (in a module) but then it doesn't work. any ideas?

" wrote:

Hi
This will make one click be yellow and double click green on sheet 1.
In the VBA editor double click sheet 1 in the Project Window. Paste in
the following:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Target.Interior.ColorIndex = 10
Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 6
End Sub

Note that the selectionChange only kicks in when you change cells, so
clicking the cell you are already on might not do anything.
You can also restrict the colour changes to a specific area on your
sheet, and not on others.
you can also make colour changes apply to all sheets in a workbook.
Post back if you need things refined a little.
regards
Paul

sadsfan wrote:
I have an interactive whiteboard, where if you touch it once this equals 1
click of the mouse and touch twice for a double click. I play a game with my
class of children which uses the mrand function from the morefunc add-in. In
the game i want to change a cell colour with one click, and change to a
different colour with a double click. Does anyone know if it is possible to
do this (the cells i want to change already have the mrand function in them)

Excell and changing cell colour
 

" wrote:

Hi
In your command button code, try to remove any activates or selections
(which trigger the change event)

e.g you can replace
Range("A1").Select
Selection.Value = 3

with
Range("A1").Value = 3

Post your button code if you are not sure what to replace.
regards
Paul

Thanks Paul here is my button code:
Sub random_numbers1()

Range("A1").Select
Range("A1:F7").Select
FormulaR1C1 = "=MRAND(42,1,42)"
Selection.FormulaArray = "=MRAND(42,1,42)"

Range("H5").Select
Range("H5:I6").Select
FormulaR1C1 = "=MRAND(4,1,12)"
Selection.FormulaArray = "=MRAND(4,1,12)"
End Sub

Excell and changing cell colour
 

Hi
Try this (untested)
Sub random_numbers1()
Range("A1:F7").FormulaArray = "=MRAND(42,1,42)"
Range("H5:I6").FormulaArray = "=MRAND(4,1,12)"
End Sub

Regards
Paul

sadsfan wrote:
" wrote:

Hi
In your command button code, try to remove any activates or selections
(which trigger the change event)

e.g you can replace
Range("A1").Select
Selection.Value = 3

with
Range("A1").Value = 3

Post your button code if you are not sure what to replace.
regards
Paul

Thanks Paul here is my button code:
Sub random_numbers1()

Range("A1").Select
Range("A1:F7").Select
FormulaR1C1 = "=MRAND(42,1,42)"
Selection.FormulaArray = "=MRAND(42,1,42)"

Range("H5").Select
Range("H5:I6").Select
FormulaR1C1 = "=MRAND(4,1,12)"
Selection.FormulaArray = "=MRAND(4,1,12)"
End Sub