Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Question Unanswered: Finding the number of colors on the color pallette

    Good morning,

    How does one find the Index Number for the colors on the color palette? I want to use them to make choices of colors in code, but have no idea how to find them.

    thank you,
    BUD

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    This might help
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by shades
    This might help
    Thank you Shades for that. I will try that while on vacation. What I was doing happens to be when you select a certain letter you get a certain color. Like this, "Y" = Green, "U" = Yellow, "N/A" = Blue, and "X" = Red and a few others.
    Code:
            Case "Y"
                iColor = 50
            Case "U"
                iColor = 6
            Case "X"
                iColor = 3
            Case "N/A"
                iColor = 48
    Have a nice one,
    BUD

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    I have code that does that. If you can explain more about what you want, I can show you a couple of ways to handle it. If you want automatic coloring based on what is in the cell, then I have code that can handle as many colors as you desire (not limited to three) and it will always change if you change what is in the cell. And it allows you to change the color scheme, without having to change any VBA code.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Posts
    533

    Color Cop

    This is a utility I use that may do what you need. Free download.

    http://www.prall.net/tools/colorcop/features.php
    Last edited by savbill; 07-27-06 at 22:59.
    ~

    Bill

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by shades
    I have code that does that. If you can explain more about what you want, I can show you a couple of ways to handle it. If you want automatic coloring based on what is in the cell, then I have code that can handle as many colors as you desire (not limited to three) and it will always change if you change what is in the cell. And it allows you to change the color scheme, without having to change any VBA code.
    Hi Shades,

    Here is a small look at what I use it for: When the legend is created showing what color is associated with what letter, all that is done then is select the letter from either a combo-box or type it in and that cell changes to one of many colors set. So if you can send the code for that which you explain it would be much appreciated, as it seems that is what I am needing.

    thanks in advance,
    BUD ")
    Attached Thumbnails Attached Thumbnails ColorSelectionSample.bmp  

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091

    Color Coding

    The attachment has three worksheets: Test, CFControl, and ColorPalette

    ColorPalette contains the 56 codes and RGB values for each (mainly for reference)

    CFControl contains the reference for the code. The code essentially looks at Column A, then returns the value in Column B to determine color used. There is a dynamic named range: rngColors

    =OFFSET(CFControl!$A$2,0,0,COUNTA(CFControl!$A:A)-1,2)

    The Code actually goes into the worksheet Test (right click the worksheet name and choose "View code", which is what you will see here. It includes the range B3:F11, which you can change as appropriate

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Conditional Formatting for more than 3 conditions
        Dim rng As Range
        ' Target is a range::therefore,it can be more than one cell
        ' For example,,someone could delete the contents of a range,
        ' or someone could enter an array..
        Set rng = Intersect(Target, Range("B3:F11"))
        If rng Is Nothing Then
            Exit Sub
        Else
            Dim cl As Range
            For Each cl In rng
                On Error Resume Next
                ' — The preceding line doesn ’t change the cell ’s background
                ' — color if the cell ’s value is not found in the range
                ' — that we specified ((rngcolors).
                cl.Interior.ColorIndex = _
                Application.WorksheetFunction.VLookup(cl.Value, _
                    ThisWorkbook.Sheets("CFControl").Range("rngColors"), 2, False)
                If Err.Number <> 0 Then
                    cl.Interior.ColorIndex = xlNone
                End If
            Next cl
        End If
    End Sub
    Now, as you change a value in the grid on the Test worksheet, the color will automatically change.

    EDIT; Just remembered where I picked this up: VBA and Macros for MS Excel by Bill Jelen, Tracy Syrstad, et al (from MrExcel.com), pages 297-298.
    Attached Files Attached Files
    Last edited by shades; 08-08-06 at 23:25.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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