PC Review


Reply
Thread Tools Rate Thread

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

 
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
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?


---
Message posted from http://www.ExcelForum.com/

 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      21st Jan 2004
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 >" <<(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>



 
Reply With Quote
 
 
 
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
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

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      21st Jan 2004
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 >" <<(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Thank you very much that is helpful.
>
> But I need the color to remain "on" once clicked, but if you click the
> same cell again, then the color turns "off" (and the number also
> dissapears).
>
> Thus, in my example, if you select March, the cell turns yellow and
> Cell B3 gets a "1." Then you select "January". Now there should be
> 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 a
> "1" next to it.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>



 
Reply With Quote
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
Perfect. Thanks a million.


---
Message posted from http://www.ExcelForum.com/

 
Reply With Quote
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
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.


---
Message posted from http://www.ExcelForum.com/

 
Reply With Quote
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
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

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
jasonsweeney
Guest
Posts: n/a
 
      21st Jan 2004
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

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mouse click on cell does not make cell active Jerry Microsoft Excel Discussion 2 1st May 2006 10:38 PM
very very very slow boot: click, click, whirrrr, click, click, whi =?Utf-8?B?RGF2aWQgUy4gR29sZGZhcmI=?= Windows XP Performance 2 4th Apr 2006 03:58 PM
the keyboard key that holds down right-click. was "accessibility and rite-click to left-click robertssubscriptions@hotmail.com Windows XP Accessibility 4 25th Feb 2006 11:19 PM
How do I double click a cell and jump to cell's referenced cell =?Utf-8?B?SmVycnlKdWljZQ==?= Microsoft Excel Misc 2 10th Sep 2005 10:24 PM
Click on cell-calendar drops down-click on date-date fills cell. . =?Utf-8?B?R2Vvcmdl?= Microsoft Excel Setup 1 15th Apr 2005 08:22 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:35 PM.