Excel 2016 color pallet number

[mark hansen]

I'm looking for a way to determine the number of the color set to cell. I have a macro that loops through creating a column of cells with the number background color... But I can't seem to find the corresponding color from the fill drop down.

I have a table and use the color number to set the color of the column header. I would like to match that color on an input screen for the same column. I can't seems to match the colors very well.

Is there an illustration of the default fill drop down of the color pallet with the color number I can refer to....

Or is there as way I can find the background color of the activecell? (figuring I can set a color from the dropdown and run some code to displays the background color number in a message box)

Looking for ideas.
Thanks,
Mark

 

[Fluff]

This will show the fill colour for the active cell

Sub markhansen()
    MsgBox ActiveCell.Interior.Color
End Sub

 

[mark hansen]

Thanks Fluff... I'm embarrassed, I didn't think it would be so simple... I didn't think in that direction.

 

[Fluff]

You're welcome & thanks for the feedback

 

[mark hansen]

Fluff,

Based on the number provided by your code, I was surprised to see the same number on many colors has the same Color number...

I created a grid in XL and used the default drop down to replicate the default fill drop down. Then I put the number of the color in each cell. Many cells have the same number! Go figure!!!! I guess that's why I couldn't get them to match something from the drop down.

I changed your code to ColorIndex and got a crazy long number. When I changed my code to ColorIndex and put that number in there, it gave me a number I can color match to the input screen. There are 70 colors in the grid do I'm thinking I'll create a VLOOKUP table to have the user select 1-70 for the column header color and it returns the ColorIndex number to change the column header. Ideally I can figure out how to automatically transfer that the input screen and the label takes on the same color as what the column header is. If not, I can at least color match from the fill drop down.

Fluff thanks again for the help to solve this little mystery that was annoying me.

Mark

 

[MARK858]

@mark hansen

There are 70 colors in the grid

ColorIndex is restricted to 56 color numbers.

Run the code below on a blank sheet, what do you get as the results for the 2 different shades?

Sub xColor()
Range("A1:C1") = Array("RGB COLOR", "COLORINDEX", "COLOR")

[A2].Interior.Color = RGB(255, 102, 204)
[A3].Interior.Color = RGB(255, 153, 255)
[B2] = [A2].Interior.ColorIndex
[C2] = [A2].Interior.Color
[B3] = [A3].Interior.ColorIndex
[C3] = [A3].Interior.Color

Columns("A:C").Columns.AutoFit
End Sub

 

[mark hansen]

Mark,

I got two different colors. The color index for both colors is 38 and the color is 13395711.
For line 2 and the color number for line 3 Is 16751103.

I Created a 7 X 10 grid and filled each cell with the corresponding color from the fill Dropdown and run the ActiveCell.Interior.Color to get the number and placed it in the cell.

I duplicated the Grid and did the same thing, but this time used ActiveCell.Interior.ColorIndex, and as expected got two different numbers.

What was unexpected is, in the Color index grid, various numbers came up more than once. (I should have realized that would happen because there are 70 colors I the dropdown, but 56 in the ColorIndex scale.)

I have screen shots, but can't attach the images...

Just another thing to scratch my head about.

Mark

 

[MARK858]

It's alright Mark, I don't need screenshots as it is behaving as I would expect it to.

My post was really just to point out that though it serves a purpose if you need to reference specific colors then maybe ColorIndex isn't your best option.