how do I: click in cell and make it change color?

[jasonsweeney]

Ok VBA prgs....

I need this solution:

A person left clicks on a cell that already has text in it. I need
that cell to (1) change color, and (2) generate a value (in a different
cell) because the person clicked in the text cell (can be value of 1 or
0).

For example, in Column A, in cells 1-12 I write in the months of the
year. If a person clicks on the cell containing "March" (cell A3) I
want that cell to change to the color yellow, and in cell B3 the number
"1" is entered.

Any ideas?

 

[Bob Phillips]

Jason,

Add this code to the worksheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A12")) Is Nothing Then
Range("A1:A12").Interior.ColorIndex = xlColorIndexNone
Target.Interior.ColorIndex = 6
Target.Offset(0, 1) = 1
End If

End Sub


--

HTH

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

 

[jasonsweeney]

Thank you very much that is helpful.

But I need the color to remain "on" once clicked, but if you click th
same cell again, then the color turns "off" (and the number als
dissapears).

Thus, in my example, if you select March, the cell turns yellow an
Cell B3 gets a "1." Then you select "January". Now there should b
two yellow cells, both with a "1" next to it. Now I click on "March
again and it turns back to no color, leaving only January yellow with
"1" next to it

 

[Bob Phillips]

Jason,

Okay try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A12")) Is Nothing Then
If Target.Offset(0,1) = 1 Then
Target.Interior.Colorindex = xlColorindexNone
Target.Offset(0,1).Value = ""
Else
Target.Interior.ColorIndex = 6
Target.Offset(0, 1) = 1
End If
End If

End Sub



--

HTH

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

 

[jasonsweeney]

Perfect. Thanks a million.

 

[jasonsweeney]

One problem:

I incorporated your code into the application I am working on....but
now I get an "Error 13" when I try and select more than one cell.....

still using my example, When I select March AND February at the same
time in one selection, I get an error 13.

 

[jasonsweeney]

Another issue: My boss now wants this solution:

Click on cell A1 once and it produces a "1" in cell B1
Click on cell A1 a second time, and it products a "2" in cell B1
Click on cell A1 a third time, and it produces a "3" in cell B1
Click on cell A1 a fourth tme, and it produces a "4" in cell B1

*** Click on cell A1 a fifth time, and it resets the cell to "".

Thus, a person can cycle through the numbners 1-4 by simply clicking o
cell A1 4 times....I think I have to select a different cell after eac
cycle so the person has to click BACK on cell A1 to trigger....thus th
last code line will be <Range("A3").select>

Any help would be greatly appreciated

 

[jasonsweeney]

Based on my description above, here is what I have tried....the proble
is that when you click on the target cell once, it instantly cycle
through all the numbers without stopping....thus I need to arrest th
cycle after one click....any help would be great.

[In cells B1:B12 I entered the months of the year]
_____________________________________

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B1:B12")) Is Nothing Then

If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1).Value = 1
Target.Offset(0, -1).Select

If Target.Offset(0, 1) = 1 Then
Target.Offset(0, 1).Value = 2
Target.Offset(0, -1).Select

If Target.Offset(0, 1) = 2 Then
Target.Offset(0, 1).Value = 3
Target.Offset(0, -1).Select

If Target.Offset(0, 1) = 3 Then
Target.Offset(0, 1).Value = 4
Target.Offset(0, -1).Select

If Target.Offset(0, 1) = 4 Then
Target.Offset(0, 1).Value = ""
Target.Offset(0, -1).Select


End If
End If
End If
End If
End If
End If

End Su