Creating a Drop Down List of Fill Colors

[SteveDale]

Hello fellow geniuses. I work in Excel 2013 and am very proficient in many things. However, I was asked a question that stumped me and I have never even thought of it.

The boss of my wife, who is not very smart when it comes to Excel and wants to cater to people who are not proficient in Excel, wants to see if its possible in creating a drop down list of fill colors to chose from to fill the cell. I know conditional formatting because I already built this for her, but the problem is they want to be able to put dates in the cells. This date, is not going to be in correlation or related to a fixed date so I cannot even create a range for conditional formatting to catch. Basically the guys would but in a date and click the drop down and SEE the color red, click it, and it will FILL the cell that red color.

I am not even sure this is possible, I know with VBA most thins are possible. I have coded many things in VBA however I have no idea of even how to approach this or combine logic to get it to work.

Help from everyone if this is possible.

 

[Logit]

.
Here's a project that should work with slight modification:

' Developed by John Walkenbach
' john@j-walk.com
' http://www.j-walk.com/ss/


Option Explicit
Public ColorValue As Variant
Dim Buttons(1 To 56) As New Class1


Function GetAColor() As Variant
'   Displays a dialog box and returns a
'   color value - or False if no color is selected
    Dim ctl As Control
    Dim ButtonCount As Integer
    ButtonCount = 0
    For Each ctl In UserForm1.Controls
'       The 56 color buttons have their Tag property set to "ColorButton"
        If ctl.Tag = "ColorButton" Then
            ButtonCount = ButtonCount + 1
            Set Buttons(ButtonCount).ColorButton = ctl
            If WorkbookIsActive Then
'               Get colors from the active workbook's palette
                Buttons(ButtonCount).ColorButton.BackColor = _
                    ActiveWorkbook.Colors(ButtonCount)
            Else
'               Get colors from this workbook's palette
                Buttons(ButtonCount).ColorButton.BackColor = _
                    ThisWorkbook.Colors(ButtonCount)
            End If
        End If
    Next ctl
    UserForm1.Show
    GetAColor = ColorValue
End Function


Function WorkbookIsActive()
'   Returns True if there's an ActiveWorkbook
    Dim x As String
    On Error Resume Next
    x = ActiveWorkbook.Name
    If Err = 0 Then WorkbookIsActive = True Else WorkbookIsActive = False
End Function


Sub Test_GetAColor1()
'   Example of using the GetAColor function
'   This sub prompts for a color, then changes
'   the color of a shape named Donut
    Dim UserColor As Variant
    Dim OldSelection As Object
    Set OldSelection = Selection
    UserColor = GetAColor()
    If UserColor <> -1 Then
        ActiveSheet.Shapes("Donut").Select
        Selection.ShapeRange.Fill.ForeColor.RGB = UserColor
        Selection.ShapeRange.Line.ForeColor.RGB = UserColor
        OldSelection.Select
    End If
End Sub


Sub Test_GetAColor2()
'   Example of using the GetAColor function
'   This sub prompts for a color, then changes
'   the color the selected cells
    Dim UserColor As Variant
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range."
        Exit Sub
    End If
    UserColor = GetAColor()
    If UserColor <> -1 Then Selection.Interior.Color = UserColor
End Sub

Download : https://www.amazon.com/clouddrive/share/8SXs7QwM8v2RlXHeyGuqIqFckBINeAIQWx7pnP49AFL

 

[My Aswer Is This]

I do believe what your wanting is possible but there may be other ways.
The user could double click on a cell having the color he wants to use and then when he changes a value in another cell that cells interior color could be changed to that color

For example we have Range("A1:A56") interior colors set to 56 different colors.

You double click on Range "A1" which has it's color set to "Red"
Then enter your date in say any cell in column "G" and the cells interior color changes to red.

Not sure this would work for you.
If this may work for you let me know.