Would you mind taking a look at this code?

jaeb4u2c

New Member
Joined
Aug 26, 2005
Messages
22
I don't think this is a partcularly difficult problem; however, I need the assistance of someone who is familiar with this kind of coding. What I want to do is use the RANDBETWEEN function to select random numbers; however, when those numbers are displayed I want them to be displayed in a color coded format. Let me explain and give you an example:

Let's say that I have the following numbers randomly selected:
7, 9, 14, 18, 21, 25, 35

What I want to do is pre-assign a color to all the numbers in the range that I specify for the RANDBETWEEN function. Let's say that I am randomly selecting numbers between 1-44. And for the numbers that are displayed above the I would have set it up so that certain numbers show up in blue and other numbers in red, e.g.,

If 7 then blue (When the #7 is displayed the cell or font color is blue)
If 9 then blue (When the #9 is displayed the cell or font color is blue)
If 14 then red (When the #14 is displayed the cell or font color is red)
If 18 then blue (When the #18 is displayed the cell or font color is blue)
If 21 then red (When the #21 is displayed the cell or font is red)
If 25 then red (When the #25 is displayed the cell or font is red)
If 35 then blue (When the #35 is displayed the cell or font blue)

I want to assign all the numbers in the range that I specify (1-44) a specific color and when that number is randomly selected I want it to be displayed in that color in that cell. Keep in mind that each cell is set to randomly select numbers between 1-44. Here is what i've come up with so far. See if you can help me complete this code so that it works and accomplishes the task intended need. Thanks.

Private Sub Worksheet_Calculate()
' Multiple Conditional Format
Dim rng As Range
' Adjust Format range to suit
Set rng = Range("B9:B31, C9:C31, D9:D31, E9:E31, F9:F31, G9:G31")
' Adjust conditions to suit
Select Case rng.Value
' BLUE
Case 3, 4, 5, 6, 11, 12, 13, 16, 18, 20, 21, 22, 24, 25, 26, 28, 29, 30, 31, 33, 34, 35, 37, 38, 39, 40, 41, 42, 44
rng.Interior.ColorIndex = 3
' RED
Case 1, 2, 7, 8, 9, 10, 14, 15, 17, 19, 23, 27, 32, 36, 43
rng.Interior.ColorIndex = 5
' No Format
Case Is <> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44
rng.Interior.ColorIndex = 0
End Select
End Sub
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try
Code:
Private Sub Worksheet_Calculate()
' Multiple Conditional Format
Dim rng As Range
' Adjust Format range to suit
Set rng = Range("B9:B31, C9:C31, D9:D31, E9:E31, F9:F31, G9:G31")
' Adjust conditions to suit
For Each ce In rng
Select Case ce.Value
' BLUE
Case 3, 4, 5, 6, 11, 12, 13, 16, 18, 20, 21, 22, 24, 25, 26, 28, 29, 30, 31, 33, 34, 35, 37, 38, 39, 40, 41, 42, 44
ce.Interior.ColorIndex = 3
' RED
Case 1, 2, 7, 8, 9, 10, 14, 15, 17, 19, 23, 27, 32, 36, 43
ce.Interior.ColorIndex = 5
' No Format
Case Is <> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44
ce.Interior.ColorIndex = 0
End Select
Next ce

End Sub


Tony
 

jaeb4u2c

New Member
Joined
Aug 26, 2005
Messages
22
In gratitude

I am very grateful for your help with this code; however, I need to ask you how to apply this code. I ask this, because I copied it and pasted it, but when I went to the worksheet and did a randbetween function nothing happened. I am very new to this so you may have to walk me through the steps to get the code to execute. I promise once you give me these steps I will be good to go. Thanks again.

Jaime
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Jamie

Assuming that you are on sheet1, then right click on the sheet tab, select view code, then paste the code. On the sheet select the range B9:G31, enter the formula =randbetween(1,44), hold down the control key, then press enter.

The numbers should color. Press the F9 key and the colors should change.

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,736
Messages
5,573,926
Members
412,555
Latest member
mark84
Top