Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts

    change cell color by clicking in Excel 2010

    I would like to change the colour of a cell by single-clicking on it, the colour to be determined by the column that the cell is in.

    In other words, when single-clicked, any cell in column 2 would turn green, a cell in column 3 would turn orange and a cell in column 4 would turn red. Right-click on any cell would remove any colour.

    The code I have found is:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Select Case Target.Interior.ColorIndex
    Case xlNone, 4: Target.Interior.ColorIndex = 3
    Case Else: Target.Interior.ColorIndex = 4
    End Select
    End Sub

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

    However, this code requires a double-click, not a single-click, repeated double-clicks change the colour and the colour does not depend on the column that the cell is in.

    I would be very grateful if someone who knows how to code in vba could modify the above code to do what I need, as set out above.

    Thank you very much in anticipation.

    useful

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Useful,

    Here's the Structure you need fix colors as necessary.

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
      If (Target.Count = 1) Then
      
         With Target.Interior
         
             Select Case Target.Column()
          
                  Case 2:  .ColorIndex = 3
                  Case 3:  .ColorIndex = 4
                  
             End Select
             
         End With 'Target.Interior
        
      End If
    
    End Sub   'Worksheet_SelectionChange
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    useful (2016-05-20)

  4. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Hi Amazing Geek!

    You are a gem (as always). Your code worked an absolute treat!

    The one thing I forgot in my previous post, was to also populate the clicked cell with 1 apart from the colour.

    Thank you very much in anticipation.

    useful

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    populate the clicked cell with 1 apart from the colour.
    ?????????

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Sorry, my shorthand is obviously too cryptic.

    What I mean is, could you please add to the code, such that in addition to changing colour when clicked, the cell also now has a number 1 in it.

    Thank you.

    useful

  7. #6
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    try this

    Code:
    Option Explicit
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
        If (Target.Count = 1) Then
        
            Target.Value = "1"
            
            With Target.Interior
            
                Select Case Target.Column()
                
                Case 2:  .ColorIndex = 3
                Case 3:  .ColorIndex = 4
                
                End Select
            
            End With 'Target.Interior
        
        End If
    
    End Sub   'Worksheet_SelectionChange

  8. The Following User Says Thank You to Gunslinger For This Useful Post:

    useful (2016-05-20)

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    column 2 would turn green, a cell in column 3 would turn orange and a cell in column 4 would turn red
    ..what about Case 4?
    ..and what if the ColorIndex isn't standard?

    I would use..
    Case 2: .Color = rgbDarkGreen
    Case 3: .Color = rgbDarkOrange
    Case 4: .Color = rgbRed

    zeddy

  10. The Following User Says Thank You to zeddy For This Useful Post:

    useful (2016-05-20)

  11. #8
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    I wondered about that, just figured he could modify the colour options etc as required after the code was doing what he wanted, fine polishing is easy later

  12. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    G.S.,

    Although this works { Target.Value = "1" } it is inefficient since the string "1" has to be converted to a numerical 1 easier to just use { Target.Value = 1 }

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. The Following 2 Users Say Thank You to RetiredGeek For This Useful Post:

    Gunslinger (2016-05-22),useful (2016-05-20)

  14. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Thank you all. You are wonderful!

    My issue is resolved and I am very grateful that the knowledgeable responses were so prompt!

    Long may you continue to give sound advice!

    useful

  15. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    269
    Thanks
    32
    Thanked 0 Times in 0 Posts
    I seem to have got excited a little too soon ... the code worked like a charm at the beginning, but when I added worksheet protection, it failed and also, it adds a 1 to every cell I click on, not just in the columns I want it to.

    A small test spreadsheet (with the VBA code) is attached here, so I can better explain what I am trying to achieve:

    - The cells in grey are protected and the ones in white can be typed in (the password is test)
    - The cells in Column C will have numbers typed in
    - The cells in Column D will either have N or Y typed in
    - The cells in Columns E, F, G and H are the only cells to have colours when clicked and the number 1 inserted, nowhere else

    I would be very grateful if someone could help me with this.

    Thank you in anticipation.

    useful
    Attached Files Attached Files

  16. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    See attached file.

    This uses conditional formatting to achieve what you have asked for.
    I assume that you can only have one choice per row for columns [E:H]
    So, if you choose, for example, 'Never', then any other choice in that row is removed etc etc etc
    Right-click will remove an existing choice.

    Also, since you said that column [D] could only be a Y or N, I added data validation for that column to only permit these choices for the cell entry.
    Similarly, for column [C], I added a 'number only' Data Validation rule.
    (I chose 'whole numbers' between 1 and 99999, but you can easily amend this rule to suit your requirement).
    I changed the 'sheet-protection' to allow cellpointer to move to protected cells. This still doesn't allow any cell entry into locked cells; if you permit selection of unlocked cells only, this will mean that any cellpointer movement using the cursor keys could unintentionally change entries in the restricted column range [E:H]

    I will be off grid for a few days, so if you need additional help I'm sure RG and others here will assist you.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-05-21 at 06:05.

  17. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    ..also, I was tempted to change the cell value from a 1 to a P
    ..if you format the cells to Windings 2, a capital P gives you a 'tick'.
    But I assumed you might be 'adding' those 1 values to give a total somewhere.
    If that were the case, you could still use P instead of 1, and use a COUNTIF formula to 'count the number of P's.

    zeddy

  18. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..so [E] to [H] can all be 1????

    ..perhaps you are right!

    zeddy

  19. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    You are correct.
    And a fact remains a fact whether I choose to believe it or not

    Let's keep the posters on their toes.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •