"IF" you can "Then" check this out.

[jaeb4u2c]

Hello:

I don't think this is a partcularly difficult problem; however, I need the assistance of someone who is familiar with the "IF" function. 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-46. 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 color will be blue)
If 9 then blue (When the #9 is displayed the cell color will be blue)
If 14 then red (When the #14 is displayed the cell color will be red)
If 18 then blue (When the #18 is displayed the cell color will be blue)
If 21 then red (When the #21 is displayed the cell will be red)
If 25 then red (When the #25 is displayed the cell will be red)
If 35 then blue (When the #35 is displayed the cell will be blue)

I want to assign all the numbers in the range that I specify a specific color and when that number is randomly selected I want it to display that color, along with the number randomly selected, in that cell.

If you could help me out on this one I will be eternally grateful.

Jaime

 

[Norie]

Jaime

Are the numbers in seperate cells?

If they are you could use Conditional Formatting with the Formula Is option.

What that formula is would depend on how you define which numbers are coloured which colour.

You could use OR, or perhaps a VLOOKUP.

 

[Smitty]

You could use a worksheet calculate event:

<font face=tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()
    <SPAN style="color:#007F00">'   Multiple Conditional Format</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#007F00">'   Adjust Format range to suit</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1")
        <SPAN style="color:#007F00">'   Adjust conditions to suit</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> rng.Value
            <SPAN style="color:#007F00">'   BLUE</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> 7, 9, 18, 35
                rng.Interior.ColorIndex = 3
            <SPAN style="color:#007F00">'   RED</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> 14, 21, 25
                rng.Interior.ColorIndex = 5
            <SPAN style="color:#007F00">'   No Format</SPAN>
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <> 7, 9, 14, 18, 21, 25, 35
                rng.Interior.ColorIndex = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Right cli8ck the sheet tab and select View Code, then paste the code in the new window that opens on the right. Just change the range("A1") to reflect your actual range.

The Select Case statement will let you adjust the format however you want.

Hope that helps,

Smitty