color selector

[Guest]

Good day,

Is there any VBA code that will pop up the Excel cell shading pallete and
allow the user to select the color. Then, I want to store the number of the
color that they have selected for later use. Kind of like the
"GetOpenFileName" method, but open the cell shading colors instead.

Thanks,

 

[Bob Phillips]

Function ReturnColorindex(Optional Text As Boolean = False) As Long
Dim rngCurr As Range
Set rngCurr = Selection
Application.ScreenUpdating = False
Range("IV1").Select
Application.Dialogs(xlDialogPatterns).Show
ReturnColorindex = ActiveCell.Interior.ColorIndex
If ReturnColorindex = xlColorIndexAutomatic And Not Text Then
ReturnColorindex = xlColorIndexNone
End If
ActiveCell.Interior.ColorIndex = xlColorIndexAutomatic
rngCurr.Select
Set rngCurr = ActiveSheet.UsedRange
End Function

 

[Tom Ogilvy]

No, not really.

If you pop it up, then when they select a color it will be provided to the
active cell.

If that is fine, then you can query the activecell for the colorindex
select.

Sub ABCD()
Application.Dialogs(xlDialogPatterns).Show
Msgbox ActiveCell.Interior.ColorIndex
End Sub