VBA get color information?

[bdepolo]

Hi Everyone,

I have a macro that highlights every other row within a selection and currently just have it set to a shade of gray. Is there a way for VBA to get the color from the fill color button in the ribbon so that I can just change that to get the macro to fill with whatever color is in there?

Thanks in advance

 

[JamesW]

Hi there,

This will open up the fill box and store the result as 'ShowColor'. You should be able to adapt this so that it works with your code. If you can't post your code with [ CODE ] [ /CODE ] tags.

Private Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As ChooseColor) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Type ChooseColor
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    rgbResult As Long
    lpCustColors As String
    flags As Long
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type


Private Function ShowColor() As Long
    Dim ChooseColorStructure As ChooseColor
    Dim Custcolor(16) As Long
    Dim lReturn As Long
    ChooseColorStructure.lStructSize = Len(ChooseColorStructure)
    ChooseColorStructure.hwndOwner = FindWindow("XLMAIN", Application.Caption)
    ChooseColorStructure.hInstance = 0
    ChooseColorStructure.lpCustColors = StrConv(CustomColors, vbUnicode)
    ChooseColorStructure.flags = 0
    If ChooseColor(ChooseColorStructure) <> 0 Then
        ShowColor = ChooseColorStructure.rgbResult
        CustomColors = StrConv(ChooseColorStructure.lpCustColors, vbFromUnicode)
    Else
        ShowColor = -1
    End If
End Function
Sub ColorTime()
    Selection.Interior.Color = ShowColor
End Sub

Source: NateO - http://www.mrexcel.com/forum/showpost.php?p=161368&postcount=3

 

[bdepolo]

Got that to work perfectly, thank you very much!