Button Color based on cell

[Thanks]

Hello
How can I change the button color based on cell contents or fill color. I
have button one sheet and I would like for it to mimic the color of a cell in
another sheet. The cell content is either "Red", "Yellow", "Green".

 

[JLGWhiz]

You would be better off using the data content of the cell as criteria
rather than the fill color of the cell, especially if the cell is filled by
conditional format.

 

[Peter T]

You can't change the colour of a Forms button though you can change the
font. (I assume you don't mean a CommandButton).

You can make a 'Bevel' Autoshape look like a button with a little
customizing and removing its lines lines. Add some text to it and assign a
macro

Assuming your Bevel is named ("AutoShape 1") try the following

Sub test()
Dim idx As Long
Dim cel As Range
Dim shp As Shape

Set cel = Range("A1")
idx = Range("A1").Interior.ColorIndex
If idx < 1 Then idx = 58

Set shp = ActiveSheet.Shapes("AutoShape 1")
shp.Fill.ForeColor.SchemeColor = idx + 7

End Sub

Above asssume Excel 2003 or earlier. In Excel 2007 would need to get the
cell's RGB colour and change Schemecolor to .RGB

Regards,
Peter T

PS, the bevel looks like a double rectangle

 

[Thanks]

I suppose it is a COmmandButton because I can manually change the color of
the button. So now what?
Thanks for your response.

 

[Peter T]

Which toolbar did you get it from.
what's its name.
How do you manually change the colour.

Regards,
Peter T

 

[Thanks]

Added from developer tool bar Inset> Button (Form Control) To change color I
click Design Mode then right click the button > Properties and then change
Back Color in properties list.

 

[Peter T]

No you didn't do that. What's its name as I asked before (assuming you
didn't change it after it was created).

Regards,
Peter T

 

[JLGWhiz]

Here is how to do it. But you will need to get you Hex codes by opening the
properties and selecting the colors you want from the palette for the back
colors.
You will need to substitute the cells, etc. to suit your purposes. The code
would go into the sheet code module. Right click the sheet tab and click
view code. You can add more elseif if you need more options.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
If UCase(Range("A1").Value) = "A" Then
Sheets(1).CommandButton1.BackColor = &HFFFF00
ElseIf UCase(Range("A1").Value) = "B" Then
Sheets(1).CommandButton1.BackColor = &HFF000
End If
End If
End Sub

P.S. The Hex code will look different because VBA deletes the leading zeros.

 

[Thanks]

Sorry it is CommandButton11

No you didn't do that. What's its name as I asked before (assuming you
didn't change it after it was created).

Regards,
Peter T

 

[Peter T]

Afraid I was a bit cryptic last time, but you would not have formatted a
Forms button via Design mode / Properties as you said you did. Anyway the
name strongly implies it is an ActiveX CommandButton.

Changing the colour of the button is simple enough but the potential problem
in Excel 2007 is to determine the apparent cell colour. If in a Table it's
difficult (but not impossible), but if the colour is applied by one of the
new Conditional Formats it's extremely difficult and way beyond what's
viable to demo (I have yet to attempt it).

The following should work if the colour was been applied via the palette or
a Style. If the cell colour returns white it's far from conclusive as to
what the apparent colour is, so perhaps better to reset to its default, but
that's up to you. A CF colour will override even a formatted colour hence
following would be inadequate.

Sub test()
Dim nClr As Long
Dim rCel As Range
Dim ole As OLEObject

Set rCel = ActiveCell
' or say
Set rCel = ActiveSheet.Range("A1")

Set ole = ActiveSheet.OLEObjects("CommandButton11")

nClr = rCel.Interior.Color

' reset to default if black or white
If nClr = vbBlack Or nClr = vbWhite Then
nClr = vbButtonFace
End If

ole.Object.BackColor = nClr

End Sub

Obviously change the cell-ref and button name to suit.

Now the second problem!
The apparent colour can change for all sorts of reasons, some of these can
be worked out in an appropriate worksheet event. However if the colour has
been manually applied as a format nothing is going to trigger the code to
change the button colour, you'll need to run run something like the above
macro.

Regards,
Peter T

 

[Thanks]

This is what I have so far.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("E34") Then
If UCase(Range("e34").Value) < 300 Then
Sheets("DASHBOARD").CommandButton11.BackColor = &HFF&
ElseIf UCase(Range("e34").Value) < 400 Then
Sheets("DASHBOARD").CommandButton11.BackColor = &HFFFF&
End If
End If
End Sub

no I realize that I should probably have an AND in there for between 300 and
400. However if I just use on CASE and change E34 <300 the button color
does not change.

 

[Thanks]

Thanks Peter
OK Working trying both solutions to see which works best for me. This is
what I have so far.

Sub test()
Dim nClr As Long
Dim rCel As Range
Dim ole As OLEObject

Set rCel = ActiveCell
' or say
Set rCel = ActiveSheet.Range("F35")

Set ole = ActiveSheet.OLEObjects("CommandButton11")

nClr = rCel.Interior.Color

' reset to default if black or white
If nClr = vbBlack Or nClr = vbWhite Then
nClr = vbButtonFace
End If

ole.Object.BackColor = nClr

End Sub

F35 is linked to a calculated cell on another page and is calculated there.
F35 is conditionally formatted as I would like for the button color. When
the value changes the CF for F35 changes but not the Button.

What now?

 

[Peter T]

You need to workout the 'state' of the *other* cell as to which, if any, of
the format coditions resolves to =True.

If a CF condition has triggered, what CF colour is applied with that CF. I
can't tell you that as I can't see your spreadsheet.

If you are using Excel2003 or earlier it will be a ColorIndex between 1-56
(come back if you don't know how to work out which colorindex)

nClr = activeworkbook.colors(myColorIndex)
than apply nClr to the button as per the example I posted

If no CF is true you need to workout the format colour of cell f35, which is
simply as per the example.

If you are using Excel2007, as I explained, it is difficult to calculate the
relevant colours. Simplest for your needs might be to copy the various
relevant colours to an image editor and return the RGB attributes. Back in
VBA do
nClr = RGB(r, g, b)

Regards,
Peter T