Prev: Run-time 438 - OptionButton troubles
Next: opening up Powerpoint slide from Excel
From: Peter T on 21 Feb 2010 05:39
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



"Peter T" <peter_t(a)discussions> wrote in message
news:eLjgR3ksKHA.6140(a)TK2MSFTNGP05.phx.gbl...
> 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
>
>
>
> "ker_01" <ker01(a)discussions.microsoft.com> wrote in message
> news:7329DEF8-03E1-4B10-A172-DA1F114A2A40(a)microsoft.com...
>>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
>>
>>
>
>


First  |  Prev  | 
Pages: 1 2 3
Prev: Run-time 438 - OptionButton troubles
Next: opening up Powerpoint slide from Excel