Results 1 to 5 of 5

Thread: Changing Cell Colour using a Button
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Changing Cell Colour using a Button

    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,450
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Changing Cell Colour using a Button

    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.
    Code:
    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Cell Colour using a Button

    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

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,450
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Changing Cell Colour using a Button

    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    444
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Cell Colour using a Button

    Brilliant thanks Joe

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •