COLOR INDEX NUMBERS

  • Thread starter Thread starter Mary Bates
  • Start date Start date
M

Mary Bates

Where can I find the color index numbers in Excel. I
would like to SumByColor, and do not know the index number
for each color.

Thanking you in advance for your assistance.

Cordially,

MB
 
Mary,

A quick way to see the current colors is to run the following code
with a blank sheet active.

Sub ShowColors()
Dim N As Long
For N = 1 To 56
Cells(N, 1).Interior.Color = ThisWorkbook.Colors(N)
Next N
End Sub

The row number is the ColorIndex value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip Pearson wrote
Sub ShowColors()
Dim N As Long
For N = 1 To 56
Cells(N, 1).Interior.Color = ThisWorkbook.Colors(N)
Next N
End Sub
Click to expand...

Cool. I saved the resulting workbook.
 
Hi Mary Bates,
If you choose another cell to check the color against
then you don't need a table and it is more accurate than trying
to guess what color it actually is. Using Chip's subroutines.
[-- http://www.cpearson.com/excel/colors.htm n--]

Count the cells with same interior color as A$3
=countbycolor(A$1:A$17,cellcolorindex(A$3,0))

Hi David Turner and Chip Pearson,
Might be better to also show the Font, see how many
font colors look black on a laptop, though boldface helps.

Sub ListColors()
'you may change the colors in color palette used in your workbook
Dim i As Long
Cells(1, 1) = "Interior": Cells(1, 2) = "Font": Cells(1, 3) = "boldface"
Columns(3).Font.Bold = True
For i = 1 To 56
Cells(i + 1, 1).Interior.Color = ThisWorkbook.Colors(i)
Cells(i + 1, 2).Font.Color = ThisWorkbook.Colors(i)
Cells(i + 1, 3).Value = "[color " & i & "]"
Cells(i + 1, 3).Font.Color = ThisWorkbook.Colors(i)
Cells(i + 1, 3).Value = "[color " & i & "]"
Cells(i + 1, 3).Font.Bold = True
Next i
End Sub

It would be helpful if there was a way of telling if the color
assignment differed from the distributed assignments, but
other than hardcoding the hex or RGB values, I don't know that
there is an indicator.

The following reference already mentioned in thread should be
of interest, especially if you don't change your workbook colors.
http://www.mvps.org/dmcritchie/excel/colors.htm



..
 
Back
Top