[RESOLVED] [Excel] Interior Colours (or colors)

[Ecniv]

Hi,

In Excel it says you can change the colour using the rgb function and setting the interior colour to that.

This seems to work. Well no it doesn't. This gets your colour and matches it to the nearest index.

Is it possible to show colours not in the index list?
If so - how?

[RobDog888]

I remember reading a thread before about a similar isue. I think it was found that only the colorindex would work as certain rgb values didnt make color changes but some did, just not all.

Try a search for the thread here in OD for more info on it.

[Ecniv]

Found your previous post - seems to be limited now (I think I recall a v97 working with custom colours, but nothing like that now).

Thanks for your help.

[Webtest]

Vince ... here is the post: [RESOLVED] EXCEL: ? Range of Cell Fill Colors

Here is a little demo to display all of the possible valid indices and their colors:

Option Explicit
'Display your COLOR PALETTE
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

I learned just today that the Visual Basic Editor/Debugger can actually change your color palette! My assistant thought he was going crazy because cells that have been 'Light Turquoise' for ages suddenly changed color and several color names changed to "color scheme". When he closed Excel and then reopened it, the colors reverted to their 'traditional' hues.

[RobDog888]

Yes, thats the thread.

Nice .colorindex code Art

[RobDog888]

I just noticed that I dont get a color for "-4142" what color is it supossed to be?

[Webtest]

Do: "MsgBox Range("A1").Interior.ColorIndex" on a newly created sheet ... "No Fill" ... seems to be exactly the same as ColorIndex = 0!

[Webtest]

In fact, if you set a cell ColorIndex to 0 in code and then immediately read it back in the next line, you'll see the "-4142" and not the 0!

[Ecniv]

Cheers. That was the thread I found.

Doesn't seem to be a point in giving vba coders the choice of putting a custom colour into the cells background then automatching to an indexed colour. Another great idea from MS...

The code to loop through the indexes is great. I think I wrote something like it that displayed the colours on a sheet, but it was about a year ago. Probably lost the code somewhere

Thanks for your help

[Webtest]

Vince ...

Any time my assistant or I does a short routine to test or proof something, we put it in a special "Cookbook" folder on our shared "Teamwork" drive. There are about 25 examples right now ... that's how I came up with the Color Chart so quickly. That's about my only claim to 'organizational skills'.

What is really nasty about the Palette system is that there are some VERY useful colors (Extra Light Yellow) that can ONLY be set programmatically! There are only 41 of the available color selections on the "Fill Color" Tool. Yuk!