VBA to change cell colors when clicked

manguy

New Member
Joined
Feb 22, 2020
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
I have some code I’ve used to change the cell fill color when a cell is clicked, if the same cell is clicked again the cell fill color is is then reset back to no fill color.

If Not Intersect(Target, Range("B2:H2")) Is Nothing Then

If Target.Interior.ColorIndex = 8 Then
Target.Interior.ColorIndex = 0

Else
Target.Interior.ColorIndex = 8
End If

End If

I would like to modify this code in two ways if it is possible. The first thing I want to do is to click a cell in a range, say any cell in B2 – H2 to change the color but if another cell in the range is clicked I would like it to automatically remove and reset the previously colored cell back to no fill color.

Also, and I may be asking a lot, is there any way to set up code in a range as described above but to limit the number of cells that can be colored at the same time, say a maximum of three cells in a range of say B2 – H2 that can be colored at the same time?

Thanks if anyone can advise.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Static Cl As Range
   
   If Not Intersect(Target, Range("B2:H2")) Is Nothing Then
      With Target.Interior
         .ColorIndex = IIf(.ColorIndex = 8, 0, 8)
      End With
      If Not Cl Is Nothing Then Cl.Interior.ColorIndex = 0
      Set Cl = Target
   End If
End Sub
 
Upvote 0
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Static Cl As Range
  
   If Not Intersect(Target, Range("B2:H2")) Is Nothing Then
      With Target.Interior
         .ColorIndex = IIf(.ColorIndex = 8, 0, 8)
      End With
      If Not Cl Is Nothing Then Cl.Interior.ColorIndex = 0
      Set Cl = Target
   End If
End Sub
Thanks very much Fluff, that works perfectly for what I want when I need to color a single cell. Do you possibly know if there is any way to set up the code in a range, so that more than one cell can be colored but to limit the number of cells that can be colored at the same time, say a maximum of three cells in a range of say B2 – H2 that can be colored at the same time?
 
Upvote 0
What happens once three cells have been coloured?
 
Upvote 0
What happens once three cells have been coloured?
I want to use it as part of an assessment, so if a question has three correct answers out of a possible 7 that are displayed in each cell in the range, the color is just for the end user to see which answers they have selected but not whether they are correct or incorrect.
 
Upvote 0
That doesn't answer my question ;)
 
Upvote 0
That doesn't answer my question ;)
Sorry, well if say a maximum of three cells were selected and the end user then clicks on a fourth cell, I would possibly like a message box to inform the user that just a maximum of three can be selected. Please let me know if you ned any further information.
 
Upvote 0
Ok, how about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim Cl As Range
   Dim i As Long
   If Not Intersect(Target, Range("B2:H2")) Is Nothing Then
      With Target.Interior
         If .ColorIndex = 8 Then
            .ColorIndex = IIf(.ColorIndex = 8, 0, 8)
         Else
            For Each Cl In Range("B2:H2")
               If Cl.Interior.ColorIndex = 8 Then i = i + 1
            Next Cl
            If i = 3 Then
               MsgBox "Max selection already made"
               Exit Sub
            Else
               .ColorIndex = 8
            End If
         End If
      End With
   End If
End Sub
 
Upvote 0
Ok, how about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim Cl As Range
   Dim i As Long
   If Not Intersect(Target, Range("B2:H2")) Is Nothing Then
      With Target.Interior
         If .ColorIndex = 8 Then
            .ColorIndex = IIf(.ColorIndex = 8, 0, 8)
         Else
            For Each Cl In Range("B2:H2")
               If Cl.Interior.ColorIndex = 8 Then i = i + 1
            Next Cl
            If i = 3 Then
               MsgBox "Max selection already made"
               Exit Sub
            Else
               .ColorIndex = 8
            End If
         End If
      End With
   End If
End Sub
Fluff, that is fantastic, just what I need. Thank you so much!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top