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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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,122,262
Messages
5,595,161
Members
413,972
Latest member
emiguy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top