Get Color from Color Fill on Formatting toolbar

  • I am trying to create a macro that will toggle on and off. It will highlight the active cell upon its selction. I can set the code to whatever color I want, but I would like it to use the color set on the menu bar. Does anyone know how to do this?


    My macro set with a random color

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        ActiveCell.Select
        With Selection.Interior
            .ColorIndex = 7
        End With
    End Sub


    I will add the toggle later.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Use current Interior Color


    Quote from chrisxs5

    I am trying to create a macro that will toggle on and off. It will highlight the active cell upon its selction. I can set the code to whatever color I want, but I would like it to use the color set on the menu bar. Does anyone know how to do this?


    Hi,


    I was just wondering did you try to change the ColorIndex to the same number as the ColorIndex on your menu bar?

  • Re: Use current Interior Color


    Quote from chrisxs5

    I am trying to create a macro that will toggle on and off. It will highlight the active cell upon its selction. I can set the code to whatever color I want, but I would like it to use the color set on the menu bar. Does anyone know how to do this?


    My macro set with a random color

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        ActiveCell.Select
        With Selection.Interior
            .ColorIndex = 7
        End With
    End Sub


    I will add the toggle later.


    Perhaps you could use this function


  • Re: Use current Interior Color


    I could be wrong and we'll have to get the OP to clarify but I read got the feeling that he wanted the colour to automatically match the colour of the active window title bar, based on what the user's colour scheme for Windows. This would be similar to selecting a colour on a userform based on the title bar colour.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Use current Interior Color


    Yes I have tried the recorder and now I have a list of the colors. What I want is if the fill color is set to red I want to use red if it is set to green I want to use it. All the ways I have tried to record only show me the index number versus how to use the color set in the fill box, on the formatting menu.


    Here is the outline of what I am doing.


    Select the color you want to use on the fill menu.
    Check a box on the menu to turn feature on.
    And now anywhere you click will be this color until the box is unchecked.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Use current Interior Color


    Correct Barbarr,
    Sorry was writing while all those came down.
    Currently playing with what Bob posted.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Use current Interior Color


    Unfortunately I don't know how to do that. :(

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Use current Interior Color


    Yep, I dont either. I think I am going in the right direction but still far away. All help still apprecaited.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Get Active Window Title Bar Color


    I changed the thread title Chris and hopefully we'll scare up that smart person here that knows the answer.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Use current Interior Color


    Quote from chrisxs5

    Yep, I dont either. I think I am going in the right direction but still far away. All help still apprecaited.


    Seeing the later posts, I don't think mine will help. It is a colour picker, and you want a 'get the colour already picked' as I read it.


    Can you use this

    Code
    CommandBars("Formatting").Controls("&Fill Color").TooltipText
  • Re: Get Active Window Title Bar Color


    Colour will come from the selected fill color on the menu bar.
    If we wanted to change the cell color in excel with no vba we would use the fill color tool. I want to take the fill color and apply to any cell I select as I select the cell.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Use current Interior Color


    Quote from norie

    Chris


    Can you verify exactly what you want to do?


    Where exactly is this colour coming from?

    Norie, he wants to get it from the Windows operating system. You know when you right-click on the desktop, Properties, Appearance. The user can select a Color Scheme that will pre-determine what the color of the Window Title Bars (both active and non-active) are or the user can go into Advanced and set the colour of the Active Window Title Bar.


    I use the default color scheme blue so my Active Window Title Bar is blue. But if I changed my color scheme to Silver, then my Active Window Title Bar is Silver.


    So what Chris wants to do is use as a fill color for a cell on his worksheet the same colour for which the Active Window Title Bar is currently set for the user on any particular computer.


    Or at least that is the way I read it. Through VBA you can get the ActiveWindow.Caption but not the colour. I know there are ways to get other information from Windows OS so I think Chris is just looking for a way to get that colour from Windows OS, even if it's in a RGB format instead of a color index, and use it in his VBA Code.

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  • Re: Get Active Window Title Bar Color


    You sure Barbarr? Sounds like the normal Fill Color button in excel.


    Chris, I think what Bob was refering to with

    Quote

    CommandBars("Formatting").Controls("&Fill Color").TooltipText

    is that is you hold the mouse over the button the tooltips displays text such as,
    Fill Color (Red)
    Fill Color (Blue)


    With this information you can parse the text with a Select statement and determine the colorindex.


    I have done some searching but can not find a way to determine the current Fill color value :(

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!