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

Discussion in 'Microsoft Excel Programming' started by jasonsweeney, Jan 21, 2004.

  1. jasonsweeney

    jasonsweeney Guest

    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/
     
    jasonsweeney, Jan 21, 2004
    #1
    1. Advertisements

  2. jasonsweeney

    Bob Phillips Guest

    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 >" <<> wrote in
    message news:...
    > 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/
    >
     
    Bob Phillips, Jan 21, 2004
    #2
    1. Advertisements

  3. jasonsweeney

    jasonsweeney Guest

    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
     
    jasonsweeney, Jan 21, 2004
    #3
  4. jasonsweeney

    Bob Phillips Guest

    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 >" <<> wrote in
    message news:...
    > 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/
    >
     
    Bob Phillips, Jan 21, 2004
    #4
  5. jasonsweeney

    jasonsweeney Guest

    jasonsweeney, Jan 21, 2004
    #5
  6. jasonsweeney

    jasonsweeney Guest

    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/
     
    jasonsweeney, Jan 21, 2004
    #6
  7. jasonsweeney

    jasonsweeney Guest

    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
     
    jasonsweeney, Jan 21, 2004
    #7
  8. jasonsweeney

    jasonsweeney Guest

    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
     
    jasonsweeney, Jan 21, 2004
    #8
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    on double click change cell color

    Guest, Oct 28, 2004, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    240
    yamefui
    May 1, 2008
  2. Guest
    Replies:
    0
    Views:
    286
    Guest
    Nov 21, 2004
  3. mhax

    Color color! Cell color!

    mhax, Aug 2, 2006, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    1,168
  4. Guest

    Copying data from cell to other on click or color change

    Guest, Aug 17, 2007, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    153
    =?Utf-8?B?SGFzc2Fu?=
    Aug 17, 2007
  5. Cyndyoxox

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

    Cyndyoxox, May 19, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    162
    Gary''s Student
    May 20, 2008
Loading...

Share This Page