Changing Cell Colour using a Button

[The Animal]

Hi

I have a costing workbook that I "fill cells" with lots of various colours using styles tabs that I have created to demonstrate various price levels. Rather than having to go to "Styles" and then dropping down all the styles etc to create the "Fill Colour" I require for that cell can I create a row of buttons that I can just click on the cell I require to fill and then just click a button to change the Fill Colour. If so how so.

Thanks so much

Stephen

 

[JoeMo]

Here's one way to do that. You need to create the buttons (code below uses a forms control command button) and change the RGB values where noted for each. I've included a function that you can use to derive the RGB values from any cell that already has a color you want to emulate.

Sub Button1_Click()
ActiveCell.Interior.Color = RGB(255, 0, 255) 'Put your RGB numbers here
End Sub
Function GetRGB(rCell) As String
'Returns the RGB values for fill of cell rcell
    Dim c As Long
    Dim R As Long
    Dim G As Long
    Dim B As Long
Application.Volatile
    c = rCell.Interior.Color
    R = c Mod 256
    G = (c \ 256) Mod 256
    B = (c \ 65536) Mod 256
    GetRGB = "R=" & R & ", G=" & G & ", B=" & B
End Function

If you want to color more than one cell at a time with a specific button, change Activecell to Selection in the code.

 

[The Animal]

Thanks so much Joe. I have the PlusNon changing to correct colour but the second one PlusNormal as below is telling me a "Compile error: Ambiguous name detected: GetRGB for this section "Function GetRGB(rCell) As String". What am I doing wrong?

Sub PlusNon()
ActiveCell.Interior.Color = RGB(255, 153, 255)
End Sub
Function GetRGB(rCell) As String
'Returns the RGB values for fill of cell rcell
Dim c As Long
Dim R As Long
Dim G As Long
Dim B As Long
Application.Volatile
c = rCell.Interior.Color
R = c Mod 256
G = (c \ 256) Mod 256
B = (c \ 65536) Mod 256
GetRGB = "R=" & R & ", G=" & G & ", B=" & B
End Function


Sub PlusNormal()
ActiveCell.Interior.Color = RGB(255, 255, 0)
End Sub
Function GetRGB(rCell) As String
'Returns the RGB values for fill of cell rcell
Dim c As Long
Dim R As Long
Dim G As Long
Dim B As Long
Application.Volatile
c = rCell.Interior.Color
R = c Mod 256
G = (c \ 256) Mod 256
B = (c \ 65536) Mod 256
GetRGB = "R=" & R & ", G=" & G & ", B=" & B
End Function

 

[JoeMo]

Install that function as a standard module (remove it from your button subs). Then find a cell that has the fill you want, say it's A1 for this example:
In any empty cell enter:
=GetRGB(A1)
and you will get three numbers, comma separated as a return. Those are the R,G and B numbers for the fill in A1. Let's say they are 100,200,130 just for example. Now create a button and use this code for it:
Sub PlusNormal()
ActiveCell.Interior.Color = RGB(100, 200, 130)
End Sub

 

[The Animal]

Brilliant thanks Joe