546 is a typo for 56.

Tim Williams wrote:

What version of excel ?

Not sure about XL2007 but previous versions only have a palette of 546
colors for cell interiors.
If you try to assign an RGB value which doesn't match one in the palette
then it just gets mapped to the "closest" one (no idea what they use to
determine which is closest)

Tim

"ker_01" wrote in message
...
I have a (26 x 60+) grid of values . I need to be able to visually identify
repeat values, so I'm looping through the cells and assigning a color
based
on the value. To be as efficient as possible, I decided to use the cell
value
to drive the color code directly; I don't care what number gets what
color,
as long as adjacent numbers don't get the same (or similar) color.

The grid values range from 1 to 80 (integers). Adjacent cells are very
likely to have adjacent values (e.g. 1 and 2, 2, and 3) up to a spread of
about 4 (or so) at one corner of the grid.

A B C D E F G H
1. 1 1 2 2 3 4 4 5
2. 1 1 2 3 3 4 5 6
3. 1 2 3 3 4 5 6 7
4. 2 3 3 4 5 6 6 8
5. 2 3 4 5 6 7 7 8
etc

so I'm trying to find intervals of RGB values based on the cell value so
that I can create the spectrum (repeat colors are fine, as long as there
are
at least a few colors inbetween for visual separation). I'm trying
different
versions of the code below, where I'm modifying the multiplier used (5,
10,
15, 20, etc) with sVal but I'm still getting situations where similar
values
(such as 1 and 2) end up with the same color (does Excel round to the
nearest
color on the pallet?).

I'm not real familiar with the RGB color wheel as it relates to the
numbers
and whatever colors Excel then uses, so I'm looking for advice on how to
fix
that one line to ensure that I won't have adjacent cells with the same
color
but different numbers.

Many thanks,
Keith

Sub ColorizeRanges()
Dim iCol As Long

For iRow = 3 To 41
For iCol = 2 To 27
uCol = ColLetter(iCol) 'separate function, returns column C to AA
sVal = (Sheet5.Range(uCol & iRow).Value)

'this is the key line:
Sheet5.Range(uCol & iRow).Interior.Color = RGB(250 - ((20 * sVal)
Mod 250), 250 - ((35 * sVal) Mod 250), ((20 * sVal) Mod 250))

Next
Next
End Sub



--

Dave Peterson