Toggle color change in cell

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I would like to color the cell if the user makes a selection and once the user selects another cell, the previous cell color changes back to the normal color as before.
I am trying the below code, but its not working.

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B48:B57")) Is Nothing Then

Target.Interior.Color = vbGreen

Else

If Intersect(Target, Range("B48: B57")) Is Nothing Then

With Range("B48: B57").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
   
End If
End If
 
End Sub

Much appreciated..
 
Last edited by a moderator:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,479
Office Version
  1. 365
Platform
  1. Windows
Not certain what you are trying to achieve, but is this it?

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Range("B48: B57")
    With .Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .ThemeColor = xlThemeColorDark1
      .TintAndShade = -0.149998474074526
      .PatternTintAndShade = 0
    End With
    If Not Intersect(Target.Cells(1), .Cells) Is Nothing Then Target.Cells(1).Interior.Color = vbGreen
  End With
End Sub
 
Solution

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Yes, that's exactly what I wanted...
What does this line refer to :
Intersect(Target.Cells(1), .Cells)
Is it referring to the cell selected ?

Thank you,
Mustafa
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,479
Office Version
  1. 365
Platform
  1. Windows
Glad it worked for you. Thanks for the follow-up. :)

What does this line refer to :
Intersect(Target.Cells(1), .Cells)
Is it referring to the cell selected ?
As you know, the code triggers when the worksheet selection changes. Since it is possible in Excel to select not just a single cell but a range of cells.
With your original code, if the user selected from A49:C50 then that selection would have 2 cells that were in your range of interest and 4 cells that were not in that range.
Since that intersection is not nothing, your code would colour all 6 cells green.

I assumed that was not what you would want so I included that code to only look at the top-left cell of any selection. That is what Target.Cells(1) does.
If only 1 cell is selected then it will be that cell.
If A49:C50 is selected then the top-left cell (A49) does not intersect with the range of interest so nothing would be made green.
If B49:C50 was selected the the top-left cell (B49) does intersect with the range so that one cell would be made green.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
Here is one I have used for years but works anyplace on the sheet. But does not maybe include all you asked for.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  11/16/2021  3:03:53 PM  EST
If Target.Cells.CountLarge > 1 Then Exit Sub
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Selection.Interior.ColorIndex = 4
End Sub
 

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
106
Office Version
  1. 365
Platform
  1. Windows
Here is one I have used for years but works anyplace on the sheet. But does not maybe include all you asked for.
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  11/16/2021  3:03:53 PM  EST
If Target.Cells.CountLarge > 1 Then Exit Sub
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Selection.Interior.ColorIndex = 4
End Sub
Thanks for the code, but I guess the one Peter_SSs works perfectly. Thank you for your help as well.?
 

Forum statistics

Threads
1,175,508
Messages
5,897,844
Members
434,682
Latest member
p1nkman

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
Top