From: ker_01 on 19 Feb 2010 20:00 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
From: Tim Williams on 20 Feb 2010 00:27 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" <ker01(a)discussions.microsoft.com> wrote in message news:7329DEF803E14B10A172DA1F114A2A40(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 > >
From: macropod on 20 Feb 2010 02:09 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" <ker01(a)discussions.microsoft.com> wrote in message news:7329DEF803E14B10A172DA1F114A2A40(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 > >
From: Dave Peterson on 20 Feb 2010 08:37 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" <ker01(a)discussions.microsoft.com> wrote in message > news:7329DEF803E14B10A172DA1F114A2A40(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 > > > >  Dave Peterson
From: Peter T on 20 Feb 2010 11:57 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:7329DEF803E14B10A172DA1F114A2A40(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 > >

Next

Last
Pages: 1 2 3 Prev: Runtime 438  OptionButton troubles Next: opening up Powerpoint slide from Excel 