Toggle between original background colour and none
 

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

--
Message posted via http://www.officekb.com

Toggle between original background colour and none
 

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" <u18959@uwe wrote in message news:92d352d7197cd@uwe...
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

--
Message posted via http://www.officekb.com

Toggle between original background colour and none
 

Ah, now I see.

Thanks a lot

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200903/1

Toggle between original background colour and none
 

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" <u18959@uwe wrote in message news:92d4220fa4c52@uwe...
Ah, now I see.

Thanks a lot

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200903/1

Toggle between original background colour and none
 

Once again, Thanks

--
Message posted via http://www.officekb.com

Toggle between original background colour and none
 

Once again, Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200903/1