asked on

Allow only one cell PER ROW to change color when dbl-clicked

Hi, Experts!
I use the code below to toggle a cell's background color and font color when double-clicked.
However, I need to allow only one cell per row to have it's background and font color changed.
Is there any way to check for this?
The behavior would be:
Cell I6 is double-clicked: color changes to blue/yellow.
Cell J6 is double-clicked: color changes to blue/yellow, and cell I6 returns to no-color/black.
Cell I7 is double-clicked: color changes to blue/yellow.
Cell I8 is double-clicked: color changes to blue/yellow.
Cell K8 is double-clicked: color changes to blue/yellow, and cell I8 returns to no-color/black.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim rngCheck As Range
   Dim lngColourIndex As Long
   lngColourIndex = 5
   Set rngCheck = Range("I6:M35")
   If Intersect(Target, rngCheck) Is Nothing Then Exit Sub
   If Target.Interior.ColorIndex = lngColourIndex Then
      Target.Interior.ColorIndex = xlColorIndexNone
      Target.Font.Color = vbBlack
   Else
      Target.Interior.ColorIndex = lngColourIndex
      Target.Font.Color = vbYellow
   End If
   Cancel = True
End Sub

Open in new window

TommySzalapski

Use this instead:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim rngCheck As Range
   Dim lngColourIndex As Long
   lngColourIndex = 5
   Set rngCheck = Range("I6:M35")
   If Intersect(Target, rngCheck) Is Nothing Then Exit Sub
   If Target.Interior.ColorIndex = lngColourIndex Then
      Target.Interior.ColorIndex = xlColorIndexNone
      Target.Font.Color = vbBlack
   Else
      Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
      Target.EntireRow.Font.Color = vbBlack
      Target.Interior.ColorIndex = lngColourIndex
      Target.Font.Color = vbYellow
   End If
   Cancel = True
End Sub

Open in new window

TommySzalapski

All I did was add a line to change the entire row to no color/black before setting the blue/yellow.

ASKER CERTIFIED SOLUTION

TommySzalapski

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

OGSan

ASKER

Thanks, Tommy. Is it possible to limit it to just the range defined? The problem is that there is some cell formatting in the other columns. THANKS! Almost there...!

OGSan

ASKER

Outstanding - you actually posted this solution before I was able to reply to your initial solution. FANTASTIC! I really appreciate the quick reply, Tommy. This really, really helps me out of a jam. Thank you SO very much.
Jeff (aka OGSan)

TommySzalapski

Yeah, I realized you might want it that way when I was testing it.

OGSan

ASKER

Yes - you read my mind quite literally! Thanks again.