Toggle color change in cell

[chriscorpion786]

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.

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..

 

[Peter_SSs]

Not certain what you are trying to achieve, but is this it?

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

 

[chriscorpion786]

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]

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]

Here is one I have used for years but works anyplace on the sheet. But does not maybe include all you asked for.

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]

Here is one I have used for years but works anyplace on the sheet. But does not maybe include all you asked for.

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.?