Generating an RGB color spectrum based on cell values (XL03)

[ker_01]

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

 

[Tim Williams]

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

 

[macropod]

Hi ker,

Here's something to get you started:
Sub ColorIt()
Dim oCel As Range, iRed As Integer, iBlu As Integer, iGrn As Integer
For Each oCel In ActiveSheet.UsedRange.Cells
With oCel
.Interior.ColorIndex = xlColorIndexNone
If .Offset(0, 1).Value = .Value + 1 Then
iRed = .Column * .Value * 31 Mod 256
iGrn = 256 - .Column * .Value * 31 Mod 256
iBlu = .Value * 127 Mod 256
.Interior.Color = RGB(iRed, iGrn, iBlu)
End If
End With
Next
End Sub

 

[Dave Peterson]

546 is a typo for 56.

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

 

[Peter T]

If I follow, and if your actual data sample looks roughly like your sample
(numbers increasing across and down the table), maybe you don't need to
customise any colours at all. Try the following -

Sub test()
Dim idx As Long
Dim rng As Range, cel As Range
Set rng = Range("A1:Z80") ' << change to suit
For Each cel In rng
With cel
idx = (.Value - 1) Mod 56 + 1
.Interior.ColorIndex = idx
End With
Next
End Sub

If any of the cells might be empty or zero, change
idx = (.Value - 1) Mod 56 + 1
to
idx = (.Value) Mod 56 + 1

If any numbers 57 to 80 are adjacent to numbers exactly 56 less, this idea
won't be quite right. Only you'll know if it can be easily adapted though,
eg say by changing the Mod number to something less than 56.

You can of course customize the 56 colour palette, either with code or
manually.

Regards,
Peter T

 

[Tim Williams]

Yes!

Thanks for the correction Dave.

Tim

546 is a typo for 56.

 

[CellShocked]

Hi ker,

Here's something to get you started:
Sub ColorIt()
Dim oCel As Range, iRed As Integer, iBlu As Integer, iGrn As Integer
For Each oCel In ActiveSheet.UsedRange.Cells
With oCel
.Interior.ColorIndex = xlColorIndexNone
If .Offset(0, 1).Value = .Value + 1 Then
iRed = .Column * .Value * 31 Mod 256
iGrn = 256 - .Column * .Value * 31 Mod 256
iBlu = .Value * 127 Mod 256
.Interior.Color = RGB(iRed, iGrn, iBlu)
End If
End With
Next
End Sub

I DLd a sheet/book somewhere with the entire (maybe) pantone/rgb table
and colored cells on it, but automating it would be cool too!

It would be cool to see a workbook like this:

Give a range or a preference for one of the three basic colors, and a
sheet gets generated with colored cells, and their numeric criteria
written in the cell, or under it. Because we cannot get them all on one
sheet.

Better still, put up the range of colors graphic 3D plot and plot the
point of your selection within the graph. The graph I refer to is the
CIE 1931 Chromaticity Diagram. Paste it as the background under your
chart, properly scaled and registered.

http://upload.wikimedia.org/wikipedia/en/5/52/Chromaticity_diagram_full.pdf

or the like.

 

[Peter T]

I DLd a sheet/book somewhere with the entire (maybe) pantone/rgb table
and colored cells on it, but automating it would be cool too!

I have a workbook that displays most of common print colours (and others).
The definitions are stored in cells, press a button to create them on the
sheet.

It would be cool to see a workbook like this:

Give a range or a preference for one of the three basic colors, and a
sheet gets generated with colored cells, and their numeric criteria
written in the cell, or under it. Because we cannot get them all on one
sheet.

I don't follow what you mean, but even XL2003- is not limited to the 56
colour palette. 10k+ unique colours can easily be displayed on a sheet in
shapes, each generated from RGB's in cells (which is how what I describe
above is achieved)

Better still, put up the range of colors graphic 3D plot and plot the
point of your selection within the graph. The graph I refer to is the
CIE 1931 Chromaticity Diagram. Paste it as the background under your
chart, properly scaled and registered.

http://upload.wikimedia.org/wikipedia/en/5/52/Chromaticity_diagram_full.pdf

or the like.

This is also possible, click on a pixel anywhere on the screen, customize a
palette colour in turn update whatever in the workbook with the new colour
in a couple of clicks (works much better in Excel 2003- than later
versions). A very small feature of a very large project that never made it
to market!

Regards,
Peter T

 

[CellShocked]

I don't follow what you mean, but even XL2003- is not limited to the 56
colour palette. 10k+ unique colours can easily be displayed on a sheet in
shapes, each generated from RGB's in cells (which is how what I describe
above is achieved)

I was just saying that reasonably showing that many entries to the user
is impractical as an informational display, so I suggested providing a
range to find a desired color. A "window" as it were.

 

[macropod]

Hi CellShocked,

Somehow I think that's 'right over the top' as far as a solution to the OP's problem goes. Even mine is perhaps more elaborate than
necessary ...

 

[Peter T]

I forgot there are 10 duplicate colours in the default palette, in the
bottom two rows of the 56 colour palette. These are sometimes known as the
chart colours although they can also be applied to shapes, and to cells with
code. These rows are not visible in the 40 colour drop-down palette in
XL2003 and earlier.

Following customizes 10 of the 16 chart colours to avoid any duplicates in
the default palette. Note too the chart colours are numbered consecutively
from 17-32 (colorIndexes in the rest of the palette are not consecutive). As
mentioned previously you can customize any/all the palette colours, you
might want to look at the top row, which being quite dark are not easily
distinguished.

Sub CustDupClrs()
Dim i As Long
Dim pal, arrIdx, arrVal
arrIdx = Array(18, 20, 25, 26, 27, 28, 29, 30, 31, 32)
arrVal = Array(10976211, 16443312, 14977173, 10048758, _
57059, 11004942, 14287066, 204, 6204972, 16750899)

pal = ActiveWorkbook.Colors
For i = 0 To UBound(arrIdx)
pal(arrIdx(i)) = arrVal(i)
Next
ActiveWorkbook.Colors = pal
End Sub

Regards,
Peter T