Self-taught Excel, have used this site for years to learn from all of you, and typically have been able to find answers in the past by reading other peoples experiences.
I have hit a wall however, as I know nothing about VBA, and there is a need to delve into this world, and have not been able to find what I am looking for.
The need is to double click a cell to change color, the contents of the cell are basically menu items, double clicking 'selects' the item.
Initially the text will be gray font. When double-clicked, the fill will change color (cyan), and the text changed to black. When double clicked again, it would revert to gray text and no fill.
We found the following worked to change the cell color.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 20, -4142, 20)
End Sub
I took a chance and added the second line, but it changes the font color only after double clicking several times, and does not revert the font back to gray.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 20, -4142, 20)
Target.Font.ColorIndex = IIf(Target.Interior.ColorIndex >= 0, -4142, 0)
End Sub
What I believe is that I need an And statement somehow, and then revert to gray after double clicking to turn it off.
Is this something you guys can help with?
Thanks a bunch!
I have hit a wall however, as I know nothing about VBA, and there is a need to delve into this world, and have not been able to find what I am looking for.
The need is to double click a cell to change color, the contents of the cell are basically menu items, double clicking 'selects' the item.
Initially the text will be gray font. When double-clicked, the fill will change color (cyan), and the text changed to black. When double clicked again, it would revert to gray text and no fill.
We found the following worked to change the cell color.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 20, -4142, 20)
End Sub
I took a chance and added the second line, but it changes the font color only after double clicking several times, and does not revert the font back to gray.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Interior.ColorIndex = IIf(Target.Interior.ColorIndex >= 20, -4142, 20)
Target.Font.ColorIndex = IIf(Target.Interior.ColorIndex >= 0, -4142, 0)
End Sub
What I believe is that I need an And statement somehow, and then revert to gray after double clicking to turn it off.
Is this something you guys can help with?
Thanks a bunch!