"open-ended" format macro

Hi,

I want to create a macro that gets me to the Custom Color window of Format Cells, then leaves me there in order to make my color selection. I don't know how to stop recording the steps at that point, or if it's even possible.

Help please

Excel 2010 Professional
Win 7Prof, 64 bit

Maybe I'm thinking too simply, but...?

Sub GetColors()
Application.Dialogs(xlDialogPatterns).Show
End Sub

I'll give it a try and let you know.

I realize I need one more step: I want to end up in the More Colors window.

How's this?

Sub test()
Dim intColor As Long

Application.Dialogs(xlDialogEditColor).Show (1)
intColor = ThisWorkbook.Colors(1)
Selection.Interior.Color = intColor

End Sub

It's good, but when I got to it, I realized that where I really need to be is on the Custom tab. Willing to make the third time the charm? Sorry for not being clear about where I want to end up.

And of course I immediately find a shorter version...

Public Sub ActiveCellColourPicker()
 
    ' // defines a Constant Long pallet index which is 32, the last Colour for the Custom pallet
    Const PALLET_INDEX As Long = 32
 
    ' // defines a Constant Long default Colour used to initialse the RGB for the Edit Colour Dialog
    Const DEFAULT_COLOUR As Long = 15790320
 
    ' // defines a Double used to store the original ActiveWorkbook Colour for the Constant PALLET_INDEX
    Dim dbOriginalColourIndex As Double
 
    ' // defines a Double used to store the selected Colour from the Edit Colour Dialog
    Dim dbNewColourIndex As Double
 
    ' // store the original ActiveWorkbook Colour for the Constant PALLET_INDEX
    dbOriginalColourIndex = ActiveWorkbook.Colors(PALLET_INDEX)
 
    ' // initialise the Edit Colour Dialog
    If Application.Dialogs(xlDialogEditColor).Show(PALLET_INDEX, _
                                                   (DEFAULT_COLOUR And 255), _
                                                   (DEFAULT_COLOUR \ 256 And 255), _
                                                   (DEFAULT_COLOUR \ 256 ^ 2 And 255)) = True _
                                                   And Not (ActiveWorkbook.Colors(PALLET_INDEX) = 1) Then
 
        ' // retrive the new ActiveWorkbook Colour for the Constant PALLET_INDEX
        dbNewColourIndex = ActiveWorkbook.Colors(PALLET_INDEX)
 
        ' // set the ActiveCell to the chosen Colour
        Selection.Interior.Color = dbNewColourIndex
 
        ' // reset the ActiveWorkbook Colour for the Constant PALLET_INDEX
        ActiveWorkbook.Colors(PALLET_INDEX) = dbOriginalColourIndex
 
    End If
 
End Sub

Perfect!

Thank you SO much.

Third time is a LOT more difficult, it seems. I struck out for a while before finding a solution crafted by a far more capable colormaster. To give credit where it's due, pretty much all of the heavy lifting here was pulled from an outside post by vba_corner. All three of the pieces below will need to be included, but the Sub you'll want to call is named CustColor().

Function PickNewColor(Optional i_OldColor As Double = xlNone) As Double
Const BGColor As Long = 13160660  'background color of dialogue
Const ColorIndexLast As Long = 32 'index of last custom color in palette

Dim myOrgColor As Double          'original color of color index 32
Dim myNewColor As Double          'color that was picked in the dialogue
Dim myRGB_R As Integer            'RGB values of the color that will be
Dim myRGB_G As Integer            'displayed in the dialogue as
Dim myRGB_B As Integer            '"Current" color
  
  'save original palette color, because we don't really want to change it
  myOrgColor = ActiveWorkbook.Colors(ColorIndexLast)
  
  If i_OldColor = xlNone Then
    'get RGB values of background color, so the "Current" color looks empty
    Color2RGB BGColor, myRGB_R, myRGB_G, myRGB_B
  Else
    'get RGB values of i_OldColor
    Color2RGB i_OldColor, myRGB_R, myRGB_G, myRGB_B
  End If
  
  'call the color picker dialogue
  If Application.Dialogs(xlDialogEditColor).Show(ColorIndexLast, _
     myRGB_R, myRGB_G, myRGB_B) = True Then
    '"OK" was pressed, so Excel automatically changed the palette
    'read the new color from the palette
    PickNewColor = ActiveWorkbook.Colors(ColorIndexLast)
    'reset palette color to its original value
    ActiveWorkbook.Colors(ColorIndexLast) = myOrgColor
  Else
    '"Cancel" was pressed, palette wasn't changed
    'return old color (or xlNone if no color was passed to the function)
    PickNewColor = i_OldColor
  End If
End Function

'Converts a color to RGB values
Sub Color2RGB(ByVal i_Color As Long, o_R As Integer, o_G As Integer, o_B As Integer)
  o_R = i_Color Mod 256
  i_Color = i_Color \ 256
  o_G = i_Color Mod 256
  i_Color = i_Color \ 256
  o_B = i_Color Mod 256
End Sub

Sub CustColor()
    Selection.Interior.Color = PickNewColor
End Sub