VBA and Cell Interior Color

[C Brandt]

I have a routine that distiles a lot of information and puts it into a large
table. I would like to color code the results from the very best, dark
green, through yellow to the worst, a dark red. I would like these colors in
about 20 steps, ten above average and 10 below. I used the following code in
a test routine to set the color:
Cells(2, 4).Select ' D2
Selection.Interior.Color = RGB(255,0,0) ' Red
Cells(3, 4).Select ' D2
Selection.Interior.Color = RGB(250,0,0) ' Red Lighter
Cells(4, 4).Select ' D2
Selection.Interior.Color = RGB(245,0,0) ' Red Lighter yet
etc.
I expected the results to be a red color, getting lighter eac row, but
instead they were all the same color.
When it gets to around 192 the color changes to a different shade the again,
right around 32.
What am I missing?

Any help would be appreciated,
Thanks,
Craig

 

[JE McGimpsey]

XL can only display the 56 colors in the Color Palette, so when you set
an RGB value, the "closest" palette value is used.

To get the 20 colors, you can modify the Color Palette
(Tools/Options/Color) with your RGB Values.

 

[C Brandt]

JE:
Thank you for your input. I am working the solution, but colors are a pain
and take time to get a smooth looking Palette.

Question: When I work with my Palette, there appears to be no order in which
of the little blocks is index 1,2,etc.
Am I missing something, or is that just the way it is?

Thanks again,

Craig

 

[JE McGimpsey]

In my version, the numbers are pretty random, though the colors tend to
go from more to less saturated as they proceed down and to the right.

It doesn't really matter, though, if you assign the colors to the
palette using RGB values, and then use RGB() to assign cell colors in
your code - XL should find exact matches.

Of course, you could use a macro to set your colors, too:

Public Sub SetColorContinuum()
Dim i As Long
With ActiveWorkbook
For i = 1 To 20
.Colors(i + 10) = RGB(260 - 5 * i, 0, 0)
Next i
End With
End Sub

Public Sub test()
Dim i As Long
For i = 1 To 20
Cells(i, 1).Interior.Color = RGB(260 - 5 * i, 0, 0)
Next i
End Sub

 

[C Brandt]

JE:

Thanks, That is close to what I did. I was looking for a smooth run from
dark green, through yellow, to dark red. I did this by hand and recorded the
RGB numbers. The big hassle is that some of the indexes are used for
standard borders and fonts.

Thanks for your help,
Craig