Solved

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

Posted on 2010-11-12
7
295 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:OGSan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34126025
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

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34126028
All I did was add a line to change the entire row to no color/black before setting the blue/yellow.
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34126034
Or if you only want it to affect columns I through M 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
      Range(Range("I" & Target.Row), Range("M" & Target.Row)).Interior.ColorIndex = xlColorIndexNone
      Range(Range("I" & Target.Row), Range("M" & Target.Row)).Font.Color = vbBlack
      Target.Interior.ColorIndex = lngColourIndex
      Target.Font.Color = vbYellow
   End If
   Cancel = True
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:OGSan
ID: 34126040
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...!
0
 
LVL 1

Author Closing Comment

by:OGSan
ID: 34126058
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)
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34126065
Yeah, I realized you might want it that way when I was testing it.
0
 
LVL 1

Author Comment

by:OGSan
ID: 34126069
Yes - you read my mind quite literally!  Thanks again.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot locate cell 15 43
where can i download QDE (Quick Date Entry) add-in for Excel? 4 23
Excel - IF criterion 2 24
need count any combinaton of 4 numbers 7 25
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question