# Would you mind taking a look at this code?

#### jaeb4u2c

##### New Member
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")
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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")
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

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

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

Replies
4
Views
373
Replies
8
Views
241
Replies
5
Views
200
Replies
20
Views
646
Replies
6
Views
587

1,212,145
Messages
6,106,219
Members
448,006
Latest member
oreo2996

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back