VB script modification (continued from yesterday)

  • Thread starter Thread starter DTLay
  • Start date Start date
D

DTLay

OK, started with...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)

If Target.Interior.ColorIndex = 50 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 50
End If

Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub

Modified to...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub

....based on code from FSt1 and Rick Rothestein. (Thanks a ton, BTW!) Works
great, except the color doesn't change on the double click anymore. It now
changes after the cell is DC'ed, then another cell is clicked. I'm playing
with the code to try to understand why, but with little effect.
 
Forgot to mention to Rick...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4)
End Sub

works as mentioned above. In one of your earlier replies, you modified to
include the original header....

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
On Error Resume Next
Target.Interior.ColorIndex = Choose(Val(Target.Value), 3, 3, 4, 4)
End Sub

....which, oddly enough, did not work. (No errors, just doesn't function.)
 
Also, the code from FSt1...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel
As Boolean)
If Target.Value = "" Then
Target.Interior.ColorIndex = xlnone
Else
If Target.Value = 1 Or Target.Value = 2 Then
Target.Interior.ColorIndex = 3
Else
If Target.Value = 3 Or Target.Value = 4 Then
Target.Interior.ColorIndex = 4
End If
End If
End If
End Sub

....generates a Runtime error 13 - type mismatch on the line

If Target.Value = "" Then

I'll take a solution to either issue. Thanks again for all the help.
 
Back
Top