change cell color by clicking in Excel 2010

Topic

New Reply

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 :confused:

Reply | Quote

Replies

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

populate the clicked cell with 1 apart from the colour.

?????????

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 :confused:

Reply | Quote

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

Reply | Quote

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

Reply | Quote

useful,

Here is some modified code that works only on the columns you had specified changing the colors and the value to 1. There is also some code added that will allow these changes when the sheet is protected

ThisWorkbook module:

Code:

Private Sub Workbook_Open()
Worksheets(“Sheet1″).Protect Password:=”test”, _
    UserInterFaceOnly:=True
End Sub

Worksheet module:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“E:H”)) Is Nothing Then
        Target.Value = 1
        With Target.Interior
        Select Case Target.Column()
            Case 5:  .Color = vbGreen
            Case 6:  .Color = vbBlue
            Case 7:  .Color = vbCyan
            Case 8:  .Color = vbYellow
        End Select
        End With
    End If
End Sub

HTH,
Maud

Reply | Quote

Hi Maud

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

..perhaps you are right!

zeddy

Reply | Quote

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

zeddy, that is what the OP is saying

Maud

Reply | Quote

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

Reply | Quote

Hi All

As you can clearly see, I’m just an keen amateur …

zeddy interpreted it all correctly, in spite of me. Clearly it makes no sense to be able to select more than one column in any one row. I should have made that clear. My apologies.

Whilst I can’t code, I can sort of read it, especially code as well commented as zeddy’s. Looking at it very carefully, I struggled to find anything relating to the colours in those cells that I copied from RG, or find the validation for the numbers, or the pick-list for the Y/N, but it all works fine. As a result, I was not surprised that when I copied the code and put it into my spreadsheet nothing worked.

Undeterred, I then tried to copy my spreadsheet into the one set up by zeddy … unfortunately that failed as well. Nothing copied and the whole process just ‘hung’.

In summary this is what I would like to achieve:

– Copy my spreadsheet into the one set up by zeddy, so that it all works;
– Change the number 1 into a centred tick, just as zeddy proposed;
– Fix the issue where clicking in the unprotected cell when the zeddy spreadsheet opens, does nothing.

Thank you in anticipation.

useful

Reply | Quote

I have managed to achieve two of the above three challenges, but I am unable to change the number 1 to a tick in the code, which for convenience, is listed below:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim zCol
Dim zRow

zCol = Target.Column ‘column number of selected cell
zRow = Target.Row ‘row number of selected cell

If zRow = 1 Then Exit Sub ‘ignore cell selection in header row
If zRow > 456 Then Exit Sub ‘ignore cell selection past last row
If zCol [h1].Column Then Exit Sub ‘ignore columns to right of col [H]

‘target column must be in range [E:H]

If Target.Count 1 Then Exit Sub ‘ignore highlighting of multiple cells

‘only single cell selected or clicked, so..

If Target.Value = 1 Then ‘existing cell already has a 1 entered, so..
Target = “” ‘clear existing entry
Else ‘otherwise..
Target = 1 ‘set cell entry to 1
End If ‘end of test for existing entry

‘—————————————-
Select Case zCol

Case 5 ‘cell in col [E] clicked
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 7) = “” ‘clear anything in [G]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 6 ‘cell in col [F] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 7) = “” ‘clear anything in [G]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 7 ‘cell in col [G] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 8) = “” ‘clear anything in [H]

Case 8 ‘cell in col [H] clicked
Cells(zRow, 5) = “” ‘clear anything in [E]
Cells(zRow, 6) = “” ‘clear anything in [F]
Cells(zRow, 7) = “” ‘clear anything in [G]

End Select

Calculate ‘<< require for recalculation of conditional formatting

End Sub 'Worksheet_SelectionChange

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim zCol

zCol = Target.Column 'column number of selected cell

If zCol [h1].Column Then Exit Sub ‘ignore columns to right of col [H]

‘target column must be in range [E:G]

If Target.Count 1 Then Exit Sub ‘ignore highlighting of multiple cells
Cancel = True

‘only single cell selected, so..

Target = “” ‘clear existing entry
Calculate ‘<< require for recalculation of conditional formatting

End Sub

I would be very grateful for your help.

Thank you in anticipation.

useful

Reply | Quote

I forgot to add that when I replace the number 1 with a tick, the colours disappear and as I can’t find where they are, I am unable to restore them.

I hope someone can assist.

Thank you in anticipation.

useful

Reply | Quote

Thank you all. I have managed to work it all out (and learned a lot in the process) so all of the issues are now resolved.

useful

Reply | Quote

See if this does what you need, i’ve changed only a couple of minor things, in the code, my edits/additions are noticeable because the comment lines are out of alignment

1. added code to exit the code if a locked cell is selected, this stops the runtime error
2. changed the value entry from 1 to the string entry P
3. altered the conditional cell formatting to trigger on “P”
4. altered the basic cell formatting to font bold and wingdings 2

sheet is re-protected, no pass

@zeddy, thanks for this, i think i might swap out the check boxes in my latest workbook in favour of this method of putting ticks in cells, as it’ll still trigger the change event without the need for macros etc, and allow the cell formatting and highlighting to work all the way through it, as well as allowing insertion of rows later

Reply | Quote