Toggle between original background colour and none

[Francois via OfficeKB.com]

I'm trying to toggle the background colour of cells between whatever colour
they were to xlColorIndexNone and back again.

My code does it for one specified colour, but I can't work out how to modify
it for any other colours it may encounter.



Option Explicit
Dim lastCell As Range

Private Sub Worksheet_BEFOREDOUBLECLICK(ByVal Target As Range, CANCEL As
Boolean)

If Target.Interior.ColorIndex = 36 Then
Target.Interior.ColorIndex = xlColorIndexNone
Else: Target.Interior.ColorIndex = 36

End If

End Sub


I realise that I need to get the colorindex somewhere, but I'm not getting it
right

Thanks in advance

 

[Bernie Deitrick]

Francois,

You need to store the value somewhere.... see the code below.


HTH,
Bernie
MS Excel MVP


Private myColor As Variant

Private Sub Worksheet_BEFOREDOUBLECLICK(ByVal Target As Range, CANCEL As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Interior.ColorIndex = xlColorIndexNone Then
Target.Interior.ColorIndex = myColor
Else
myColor = Target.Interior.ColorIndex
Target.Interior.ColorIndex = xlColorIndexNone
End If

End Sub

 

[Francois via OfficeKB.com]

Ah, now I see.

Thanks a lot

 

[Bernie Deitrick]

Francois,

You could also keep track of which cell you had cleared of color, to refill it automatically.

Private myColor As Variant
Private myAddress As String

Private Sub Worksheet_BEFOREDOUBLECLICK(ByVal Target As Range, CANCEL As Boolean)
If Target.Interior.ColorIndex = xlColorIndexNone Then
Target.Interior.ColorIndex = myColor
Else
If myAddress <> "" Then Range(myAddress).Interior.ColorIndex = myColor
myAddress = Target.Address
myColor = Target.Interior.ColorIndex
Target.Interior.ColorIndex = xlColorIndexNone
End If
End Sub


Not sure if that is in your workflow, but just an idea...

HTH,
Bernie
MS Excel MVP

 

[Francois via OfficeKB.com]

Once again, Thanks

 

[Francois via OfficeKB.com]

Once again, Thanks