Changing font color in cell on double-click

Question

In Excel (using VBA) I want to change the color of the check marks from gray to green on double-click, then back to gray again on double-click (basically a toggle). Current code is below - NOTHING I have tried has worked. Please help.

Here is the image. The font is Wingdings, character code is 252, font colors are #BEBEBE and #008000. I want those gray check marks to change to green on double click and if double clicked again, back to gray. This is in a range of cells (B7 to C150).

screenshot

CODE:

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

If Target.Column = 2 Then
Cancel = True
End If

If Target.Column = 3 Then
Cancel = True
End If

With Target.Font.Color
    If Not .Font.Color = vb15 Then
        .ColorIndex = vb10
    ElseIf Not .Font.Color = vb10 Then
        .ColorIndex = vb15
    End If
End With

End Sub

Thanks for any help!!

Vityata · Accepted Answer · 2018-02-16 16:17:37Z

3

This will work, if the color is red or blue initially:

Option Explicit

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

    With Target
        If .Column <> 2 And .Column <> 3 Then Exit Sub
        Cancel = True

        If .Font.Color = vbBlue Then
            .Font.Color = vbRed
        ElseIf .Font.Color = vbRed Then
            .Font.Color = vbBlue
        End If
    End With    
End Sub

I could not find vb10 or vb15, I guess these are some font colors generated by you. The idea in the event is to Exit Sub, if you are not happy with the condition.

Even better, if you decided to use Select Case with default option:

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

    With Target
        If .Column <> 2 And .Column <> 3 Then Exit Sub
        Cancel = True

        Select Case .Font.Color
            Case vbBlue
                .Font.Color = vbRed
            Case vbRed
                .Font.Color = vbBlue
            Case Else
                .Font.Color = vbBlue
        End Select
    End With    
End Sub

edited Feb 16, 2018 at 16:17

answered Feb 16, 2018 at 16:05

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user4039065 Over a year ago

You are missing a Cancel = True or the double-click could initiate an in-cell edit.

user4039065 Over a year ago

My bad; I thought it was the same as the OP's. Shouldn't you still have one to dismiss the right-click menu?

Vityata Over a year ago

@Jeeped - that is strange, and I thought that the OP's was with right click... Thanks, I have edited.

twoawesomedogs Over a year ago

It did! Thank you! I had to add Cancel = True because other cells need different actions, and added the RGB(X,Y,Z) for the color codes, but the rest was perfect. Thanks!!

twoawesomedogs Over a year ago

I added the following code. I know I could have tightened this up but I'm a newbie at it so I separate things to make it easier for now: If Target.Column = 2 Then Cancel = True End If If Target.Column = 3 Then Cancel = True End If

|

João Gonçalves · Accepted Answer · 2018-02-16 16:21:18Z

0

your problem here is the colors. Use one of the following ways to define and verify the color.

Target.Font.Color = -16776961

Target.Font.Color = vbRed

Target.Font.Color = RGB(255, 0, 0)

Additionally you should only have target in your with command.

With Target
    If Not .Font.Color = vb15 Then
        .ColorIndex = vb10

answered Feb 16, 2018 at 16:21

João Gonçalves

3051 silver badge10 bronze badges

Collectives™ on Stack Overflow

Changing font color in cell on double-click

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related