Get value of FillColour button

[mdmackillop]

I use this simple code to toggle cell fill colour
[VBA]Sub Yellow()
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = xlNone
Else
Selection.Interior.ColorIndex = 6
End If
End Sub[/VBA]
How do I change it to use the last colour set by the FillColour button? I see it "remembers" the last colour used.

[Simon Lloyd]

Perhaps
[VBA]Public LC as Long

Sub Yellow()
LC= Selection.Interior.ColorIndex
If Selection.Interior.ColorIndex = 6 Then
Selection.Interior.ColorIndex = xlNone
Else
Selection.Interior.ColorIndex = 6
End If
End Sub

[/VBA]you can then use LC in any code/function, have i missed the mark on what you are looking for?

[Bob Phillips]

Malcolm,

The fill colour index is not exposed in the OM, and there is no event to tap into.

This question was asked a while back in OzGrid, and I came up with this technique, using the TooltipText and parses it. I have added all colours, I leave it to yoiu to complete

[vba]

Function GetColourIndex()
Dim sText As String
Dim iPos As Long
Dim iColorIndex As Long

sText = CommandBars("Formatting").Controls("&Fill Color").TooltipText
iPos = InStr(sText, "(")
If iPos > 0 Then
sText = Mid(sText, iPos + 1, InStr(sText, ")") - iPos - 1)
Select Case sText
Case "Automatic": iColorIndex = xlColorIndexNone
Case "Black": iColorIndex = 1
Case "White": iColorIndex = 2
Case "Red": iColorIndex = 3
Case "Bright Green": iColorIndex = 4
Case "Blue": iColorIndex = 5
Case "Yellow": iColorIndex = 6
Case "Pink": iColorIndex = 7
Case "Turquoise": iColorIndex = 8
Case "Green": iColorIndex = 10
Case "Dark Blue": iColorIndex = 11
Case "Dark Yellow": iColorIndex = 12
Case "Violet": iColorIndex = 13
Case "Teal": iColorIndex = 14
Case "Gray - 25%": iColorIndex = 15
Case "Gray - 50%": iColorIndex = 16
Case "Light Turquoise": iColorIndex = 20
Case "Dark Red": iColorIndex = 30
Case "Light Blue": iColorIndex = 41
Case "Orange": iColorIndex = 46
Case "Sea Green": iColorIndex = 50
Case "Brown": iColorIndex = 53
End Select
GetColourIndex = iColorIndex
End If
End Function
[/vba]

[mdmackillop]

Very clever. Thanks Bob.

[Kenneth Hobs]

Something like this can be used. In the referenced link, there is an API method but choosing a custom color will fail. ColorIndex is not as reliable as Color.
[VBA]'http://www.mrexcel.com/forum/showthread.php?t=370318
Sub SetTabColor()
Dim r As Range, rc As Long
Set r = Range("h100")
r.Select
rc = r.Interior.Color
With Application.CommandBars("Fill Color")
.Visible = True
.Position = msoBarFloating
.Left = r.Offset(0, 1).Left
.Top = r.Top - r.Top
Do
DoEvents
Loop Until .Visible = False
End With
ActiveSheet.Tab.ColorIndex = r.Interior.ColorIndex
r.Interior.Color = rc
End Sub[/VBA]