Would you mind taking a look at this code?

[jaeb4u2c]

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

 

[acw]

Hi

Try

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]

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]

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