changing cell color with "cell buttons"?

[fusionwerks]

heres what i want to do...

assign/fill cell b2 with a specific color
assign/fill cell d2 with a different color
assign/fill cell f2 with another

select a group of cells (b4-b7) and while selected, be able to then click cell b2 and the color of the selected cells will change to whatever color i have designated in cell b2.

i am trying to make a user friendly type of timeline spreadsheet where the user can just select a group of dates along the timeline and color them without having to look for the matching color in the color picker. i will have red, green, blue, and no fill, so if the dates change they can "erase" them with no fill.

in essance the colored cells would just be shortcut "buttons" they would not have any editable type or function to them

make sense? i just need to know the process, but a sample code (if necissary) would be appriciated

 

[wigi]

Hello

Make a selection, then run this small macro:

Sub ColorMySelection()

    Selection.Interior.ColorIndex = Application.InputBox("Please select the cell with the color of your liking", "Color picker", Type:=8).Cells(1).Interior.ColorIndex

End Sub

 

[fusionwerks]

close, but i would just like a 2 click process.

select cells-click colored cell above-and done

but it will work if nothing else is possible

 

[wigi]

Then you will have to work with a Worksheet_SelectionChange event, and a public variable holding the selection you made each time. If the event detects that you select a "colored" cell (a cell button as you call it), then color the other cells - if not, do nothing.

 

[fusionwerks]

wow...you lost me there. i understand some of VBA but i dont know what your talking about. Pardon my ignorance

 

[wigi]

wow...you lost me there. i understand some of VBA but i dont know what your talking about. Pardon my ignorance

That's no problem I will wait until you have read about VBA and worked yourself through some coding.

 

[jayp777]

I too have the same problem and would love someone to show us how to accomplish this cell button! Thanks

 

[Kenneth Hobson]

In a Module:

Sub ColorMySelection()
  Range("B2").Interior.Color = vbRed
  Range("D2").Interior.Color = vbBlue
  Range("F2").Interior.Color = vbYellow
  Selection.Interior.ColorIndex = Application.InputBox("Please select the cell with the color of your liking", _
    "Color picker", Type:=8).Cells(1).Interior.ColorIndex
End Sub

In the worksheet, add an ActiveX button from the Insert menu of the Developer tab. Doubleclick the button and add the ColorMySelection. e.g.

Private Sub CommandButton1_Click()
  ColorMySelection
End Sub

 

[Jaafar Tribak]

This doesn't require a Button , it works just by selecting the range B4:B7 and single clicking the ColorPicker cells.

The ColorPicker cells are B2 :Red D2 :Green F2: Blue .

Code in the worksheet module :

Option Explicit

Private Prev As Range
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lWord As Long, hWord As Long
    Dim oColorPickerCells As Range
    Dim oFilledCells As Range
    
    Set oColorPickerCells = Union(Range("b2"), Range("d2"), Range("f2"))
    Set oFilledCells = Range("b4:b7")
    
    Call GetHiLoword(GetKeyState(VBA.vbKeyLButton), lWord, hWord)
    
    If hWord = 0 Then
        If Union(Target, oColorPickerCells).Address = _
            oColorPickerCells.Address Then
            If Prev.Address = oFilledCells.Address Then
                oFilledCells.Interior.Color = Target.Interior.Color
            End If
        End If
    End If
    
    Set Prev = Target

End Sub


Private Sub GetHiLoword _
(lParam As Long, ByRef loword As Long, ByRef hiword As Long)
 
    loword = lParam And &HFFFF&
    hiword = lParam \ &H10000 And &HFFFF&
 
End Sub