Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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 -- Cheers macropod [Microsoft MVP - Word] "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
Yes! Thanks for the correction Dave. Tim "Dave Peterson" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
On Sat, 20 Feb 2010 18:09:26 +1100, "macropod"
wrote: 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/wikipedi...agram_full.pdf or the like. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ... On Sat, 20 Feb 2010 18:09:26 +1100, "macropod" wrote: 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! 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/wikipedi...agram_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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
On Sat, 20 Feb 2010 21:36:16 -0000, "Peter T" <peter_t@discussions
wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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 ... -- Cheers macropod [Microsoft MVP - Word] "CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message ... On Sat, 20 Feb 2010 18:09:26 +1100, "macropod" wrote: 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/wikipedi...agram_full.pdf or the like. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Generating an RGB color spectrum based on cell values (XL03)
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@discussions wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell fill color spectrum | Excel Programming | |||
Change formulas into values based on cell color | Excel Programming | |||
Q: How to color a cell based on values in two cells | Excel Programming | |||
In excell be able to Sum values based on the color of the cell | Excel Worksheet Functions | |||
Problems generating an html link based on worksheet values | Excel Programming |