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

"Casey" <(E-Mail Removed)> wrote in message
news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
>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

Thanx, works perfect.....Casey

"N10" wrote:

>
> "Casey" <(E-Mail Removed)> wrote in message
> news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
> >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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
>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

> 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

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 are: 3,4,5,6, and 29.

"N10" wrote:

>
> "Casey" <(E-Mail Removed)> wrote in message
> news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
> >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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:B5F03634-12FC-4041-959F-(E-Mail Removed)...
> 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 are: 3,4,5,6, and 29.
>
> "N10" wrote:
>
>>
>> "Casey" <(E-Mail Removed)> wrote in message
>> news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
>> >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
>>
>>
>>

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
<(E-Mail Removed)> 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 are: 3,4,5,6, and 29.
>
>"N10" wrote:
>
>>
>> "Casey" <(E-Mail Removed)> wrote in message
>> news:C7BC972D-1688-44A1-97AA-(E-Mail Removed)...
>> >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
>>
>>
>>