[RESOLVED] EXCEL: ? Range of Cell Fill Colors

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA

As near as I can tell, the range of Fill Colors for Cells is from 0 to 56, and -4142. Is this all I get? No custom color fill?

By the way, here is a macro to build a map of Color Index values in Column A, and Column B filled with that color. Run it on a blank sheet:

Option Explicit
'Display your COLOR PALLETTE
Sub Macro1()
Dim i As Integer
Dim jRow As Integer

'Start at the first row
jRow = 1

'This is a funny Color Index, but it works!
i = -4142
'Load the funny color
Cells(jRow, "A").Value = i
Cells(jRow, "B").Interior.ColorIndex = i

'Walk down the sheet putting the index in A and coloring B
For i = 0 To 57
    jRow = jRow + 1
    On Error GoTo ERROR_COLOR_INDEX
        Cells(jRow, "A").Value = i
        Cells(jRow, "B").Interior.ColorIndex = i
    On Error GoTo 0
Next i
Exit Sub
'
ERROR_COLOR_INDEX:
    Cells(jRow, "A").Value = i
    Cells(jRow, "B").Value = "END"
    End
    
End Sub

[RobDog888]

Instead of the .ColorIndex property use the .Color property with an RGB color value.

.Color = RGB(0, 255, 0)

[Static]

u are just full of info today!! writing that one down as well LOL

I need to give some reps to others so I can hit u again!

[mikeyc1204]

Today? Rob has made me change my underwear more often than my washing machine can handle.

[RobDog888]

Thanks guys. Its always nice to hear positive feedback for all my efforts. I have been side tracked for the past couple of months and now getting ready to get back deep into programming so be warned and make sure your wearing some Depends for protection.

[zaza]

Incidentally, I listed all the colours in an Enum here...

Also, you can use the Pattern property to give it stripes or a grid.


zaza

[Webtest]

Instead of the .ColorIndex property use the .Color property with an RGB color value.

.Color = RGB(0, 255, 0)

Thanks for the tip RobDog, but it is an "Optical Delusion". I was playing with light gray, but the lightest gray I can get is "aCell.Interior.Color = RGB(223, 223, 223)". When I increment to "(224, 224, 224)" the cell goes from medium gray to white, and of course is white for all values from 224 through 255.

I settled on a light yellow (ColorIndex = 19) that results in a fairly pale gray on a B&W laser printer. Reading back the Color for a cell at ColorIndex = 19 yields an RGB value of (255, 255, 204). Changing the Blue value from 204 to 229 does nothing. It switches to White from 230 to 255. So, we are essentially still stuck with the ColorIndex pallette for cell fill, but thanks for the insight.

[RobDog888]

Shouldnt it be an "Optical Illusion" ? I didnt test it with such a detailed color range so thanks for letting us know its limitation.

[RobDog888]

Oh I just remembered that when I was creating custom labels for appointments in Outlook there were only certain colors that would display in terminal server because of a 256 color depth desktop. Are you running True color color depth?

[Webtest]

RobDog ...

The quotes are significant ... "Optical Delusion" ... Thinking you're getting something really neat visually, but not quite ...

I was going to mention it but forgot ... (Senior Moment) ...
Display Properties > Settings > Display > Colors > True Color (32 bit)

We are definitely limited to a pallette for Cell Fill.

[RobDog888]

Yes, graphics are limited in all of VBA. No picturebox control, no drawing on a userform, etc.