Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
I am trying to fill a grid of equal sized cells with random colors, or colors
attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
"Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey Hi Casey Try this then adpat to your needs Sub colorit() Dim task As Range Dim myvalue Set task = Range("A1:l32") For y = 1 To 5 For Each Cell In task Randomize myvalue = Int((56 * Rnd) + 1) Cell.Interior.ColorIndex = myvalue Next Next ' End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
Thanx, works perfect.....Casey
"N10" wrote: "Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey Hi Casey Try this then adpat to your needs Sub colorit() Dim task As Range Dim myvalue Set task = Range("A1:l32") For y = 1 To 5 For Each Cell In task Randomize myvalue = Int((56 * Rnd) + 1) Cell.Interior.ColorIndex = myvalue Next Next ' End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
Try something like
Sub ColorCells() Dim N As Long Dim CI As Long For N = 1 To Range("MyRange").Cells.Count CI = Int((56 * Rnd) + 1) Range("MyRange").Cells(N).Interior.ColorIndex = CI Next N End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
For Each Cell In task
Randomize The Randomize statement should not be executed more than once for the lifespan of the object that is executing your code. Doing so more often actually makes the data less random than if Randomize is only run once. For so few colors, and the probable use the OP wants to use the Rnd function for, it will more than likely not matter here, but it is the concept that is important to know. I can't demo it within Excel because I am not aware of an available object where I can set the color of individual pixels (like a PictureBox control in the compiled version of VB for those who have worked with that language); however, I do have a VB program that demonstrates this fact visually. For those of you having access to the compiled versions of VB5 or VB6, here is posting I have offered over in the compiled VB newsgroups in the past that demonstrates this fact... Running Randomize multiple times ends up producing a less random set of numbers. To see the problem visually, use this code (which is a modification of a routine Bob Butler once posted). Start a new project and put two PictureBox'es on your Form (use the default names for everything and placement of the PictureBox'es is not important). Paste the following code into the Form's code window. The PictureBox on the left results from using Randomize only once, the one on the right uses it repeatedly. Both PictureBox displays are produced from the same looping code with the only difference being the use of the Randomize statement Ignoring the pronounce vertical areas (not sure what that is, probably some kind of boundary rounding problem), for which one does the distribution of colors look more "random"? Rick Const SCALESIZE = 3 Private Sub Form_Load() Picture1.ScaleMode = 3 Picture2.ScaleMode = 3 Randomize Picture1.Move 0, 0, _ 128 * Screen.TwipsPerPixelX * SCALESIZE, _ 128 * Screen.TwipsPerPixelY * SCALESIZE Picture2.Move Picture1.Width, 0, _ 128 * Screen.TwipsPerPixelX * SCALESIZE, _ 128 * Screen.TwipsPerPixelY * SCALESIZE Me.Width = 2.02 * Picture1.Width Me.Height = 1.1 * Picture1.Height End Sub Private Sub Picture1_Paint() Dim i As Long Dim j As Long Dim colr As Long Dim bitmask As Long For i = 0 To Picture1.ScaleHeight Step SCALESIZE For j = 0 To Picture1.ScaleWidth Step SCALESIZE colr = Rnd * 16711680 Picture1.Line (j, i)-Step(SCALESIZE, _ SCALESIZE), colr, BF Next j Next i For i = 0 To Picture2.ScaleHeight Step SCALESIZE For j = 0 To Picture2.ScaleWidth Step SCALESIZE Randomize colr = Rnd * 16711680 Picture2.Line (j, i)-Step(SCALESIZE, _ SCALESIZE), colr, BF Next j Next i End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
This works if you just want a random color of the first 5 colors in the color
index list, but if I wanted a radom color out of a specific 5 colors how could I adapt this? The 5 color indexes I want a 3,4,5,6, and 29. "N10" wrote: "Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey Hi Casey Try this then adpat to your needs Sub colorit() Dim task As Range Dim myvalue Set task = Range("A1:l32") For y = 1 To 5 For Each Cell In task Randomize myvalue = Int((56 * Rnd) + 1) Cell.Interior.ColorIndex = myvalue Next Next ' End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
I can't see the entire thread (actually, I see only the message I am
responding to), but I think this macro will do what you ask (just put your color indexes in the Array function call and set the range of cells to color in the Set statement)... Sub ColorTheRange() Dim RangeToColor As Range, Cell As Range, Indexes() As Variant Randomize Indexes = Array(3, 4, 5, 6, 29) Set RangeToColor = Range("A1:l32") For Each Cell In RangeToColor Cell.Interior.ColorIndex = Indexes(Int(((UBound(Indexes) - _ LBound(Indexes) + 1) * Rnd) + LBound(Indexes))) Next End Sub As written, the code will handle any number of color index assignments (to a maximum of 56) in the Array statement list and the rest of the code will work correctly with them. -- Rick (MVP - Excel) "honeybee129" wrote in message ... This works if you just want a random color of the first 5 colors in the color index list, but if I wanted a radom color out of a specific 5 colors how could I adapt this? The 5 color indexes I want a 3,4,5,6, and 29. "N10" wrote: "Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey Hi Casey Try this then adpat to your needs Sub colorit() Dim task As Range Dim myvalue Set task = Range("A1:l32") For y = 1 To 5 For Each Cell In task Randomize myvalue = Int((56 * Rnd) + 1) Cell.Interior.ColorIndex = myvalue Next Next ' End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do i fill cells with random color?
Try something like
Sub AAA() Dim Colors As Variant Dim N As Long Dim C As Long Colors = Array(3, 4, 5, 6, 29) For N = 1 To 10 C = Colors(Int((UBound(Colors) - LBound(Colors) + 1) * _ Rnd + LBound(Colors))) Cells(N, 1).Interior.ColorIndex = C Next N End Sub Change the values within the Array function to the color index values you want to choose from. The code within the loop picks a random value from the values within the Colors array and assigns it to a cell. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Sun, 23 May 2010 19:35:03 -0700, honeybee129 wrote: This works if you just want a random color of the first 5 colors in the color index list, but if I wanted a radom color out of a specific 5 colors how could I adapt this? The 5 color indexes I want a 3,4,5,6, and 29. "N10" wrote: "Casey" wrote in message ... I am trying to fill a grid of equal sized cells with random colors, or colors attached to a random number. I can fill the grid with random numbers easily enough, it's the colors i want. Thanx....Casey Hi Casey Try this then adpat to your needs Sub colorit() Dim task As Range Dim myvalue Set task = Range("A1:l32") For y = 1 To 5 For Each Cell In task Randomize myvalue = Int((56 * Rnd) + 1) Cell.Interior.ColorIndex = myvalue Next Next ' End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I fill a block of 300 cells with non-repeating random #s | Excel Discussion (Misc queries) | |||
Fill and Font Color Drop Downs Mostly White with Random Colors | Excel Discussion (Misc queries) | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
fill random cells | Excel Programming | |||
fill random cells | Excel Programming |