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
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