After code has selected 6 cells randomly have the code sums up that row

[UltimateNeo]

Hello the code i have selects 6 different cells randomly for rows 6 - 30. Once it has done this i want it to sum the rows individually, and it has to meet >=111 and <=170. If possible change the background color of the cell if it meets >=111 <+140
and a different cell fill color for >=141 to <=170.

Dim NumArr As Object, i As Long, j As Long, rng As Range, cell As Range
Application.ScreenUpdating = False
With Me
    Set NumArr = CreateObject("System.Collections.ArrayList")
        If .CboxHot.Value = "All" Then
             Set rng = .Range("All")
        ElseIf .CboxHot.Value = ("Cold Only") Then
             Set rng = .Range("ColdNumbers")
        ElseIf .CboxHot.Value = ("Cold + Hot") Then
             Set rng = .Range("ColdHotNumbers")
        ElseIf .CboxHot.Value = ("Hot Only") Then
             Set rng = .Range("HotNumbers")
        ElseIf .CboxHot.Value = ("Non Cold Or Hot") Then
             Set rng = .Range("NonHotColdNumbers")
        End If
     
        
        If (.Shapes("MonOn").Visible = True) Or (.Shapes("WedOn").Visible = True) Or (.Shapes("SatOn").Visible = True) And .CboxHot.Value = "All" And .cboxodd.Value = 1 Then

           
         'Set NumArr = CreateObject("System.Collections.ArrayList")
         
        ' Set rng = .Range("All")
               For i = 6 To 30
               NumArr.Clear
               j = 1
               Do Until j > 1
                  Set cell = RdmCell(rng)
                  ' Consider only ODD numbers
                  If cell.Value Mod 2 = 1 Then
                     If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                        NumArr.Add cell.Value
                        j = j + 1
                     End If
                     
                  End If
                  Loop
               Cells(i, 2).Resize(, 1) = NumArr.toarray
               
            Next i
            For i = 6 To 30
               NumArr.Clear
               j = 1
               Do Until j > 5
                  Set cell = RdmCell(rng)
                  ' Consider only Even numbers
                  If cell.Value Mod 2 = 0 Then
                     If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                        NumArr.Add cell.Value
                        j = j + 1
                     End If
                  End If
               
               Loop
               Cells(i, 3).Resize(, 5) = NumArr.toarray
               Next i

        ElseIf (.Shapes("MonOn").Visible = True) Or (.Shapes("WedOn").Visible = True) Or (.Shapes("SatOn").Visible = True) And .CboxHot.Value = "All" And .cboxodd.Value = 2 Then

        '  Set NumArr = CreateObject("System.Collections.ArrayList")
         
         'Set rng = .Range("All")
             For i = 6 To 30
                NumArr.Clear
                j = 1
                Do Until j > 2
                   Set cell = RdmCell(rng)
                   ' Consider only ODD numbers
                   If cell.Value Mod 2 = 1 Then
                      If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                         NumArr.Add cell.Value
                         j = j + 1
                      End If
                      
                   End If
                   Loop
                Cells(i, 2).Resize(, 2) = NumArr.toarray
                
             Next i
             For i = 6 To 30
                NumArr.Clear
                j = 1
                Do Until j > 4
                   Set cell = RdmCell(rng)
                   ' Consider only Even numbers
                   If cell.Value Mod 2 = 0 Then
                      If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
                         NumArr.Add cell.Value
                         j = j + 1
                      End If
                   End If
                
                Loop
                Cells(i, 4).Resize(, 4) = NumArr.toarray
                Next i

  Function RdmCell(rng As Range) As Range
        Set RdmCell = rng.Cells(Int(Rnd * rng.Cells.Count) + 1)
    End Function

Thank you for any help

[Keebellah]

Hi there.
With the code I see that you relate to shapes and similar objects.
Have you tried to think that it's impossible for someone to rebuild that on a sheet to help you.
Try and attach the file with the macro code, shapes and everything and also include a sample result of what you want to achieve.
Don't send others down a guessing trail to get lost in all manner of answers.
The 'random select' where do you randomize the numbers to be selected?

[UltimateNeo]

I lost my file became corrupt on a USB stick so I have attached a simplified version.

As u can see I randomly select 6 cells and then sum them if the sum is not >=111 and <=170 then repeat the selection for that row
if it does then move to next row (i+1)

If possible to change the background colour of the cell for when the sum equals 111 - 140 then a gradient of that colour for values of the sum equaling 141 - 170?

Thank you for your help

[Keebellah]

Will see what I can do for you BUT

Why does the dropdown only show 1s?

[UltimateNeo]

Will see what I can do for you BUT

Why does the dropdown only show 1s?

I might have forgotten to fill series on the z12:z20

It might have just copied. I thought i did fill series on CboxOdd

[Keebellah]

Looking at that I don't understand it, sorry

[UltimateNeo]

Looking at that I don't understand it, sorry

It is just to select how many in the array are odd numbers. It goes from 1 to 8. Sorry i wasnt home earlier.

Think of it as having six numbers like the lottery randomly selected. You are controling how many are odd and even and what these 6 numbers add upto.

If the randomly selected numbers dont add upto >=111 <=170 then re select that line till they fall in that range. Then move onto the next row/line. Does that help explain it at all?

Think of the monon and tueson as different games, that could have 7 or 8 numbers selected using different named ranges for example a range might be 1-45, another 1-35 and 1-50

So depending on the tab selected eg monon then u select allnumbers or partnumbers depending on the range u want to play. Then select how many odd numbers u want the rest will be even.

[Keebellah]

Okay, at least I now know which range

[UltimateNeo]

Maybe something like this

if cells(i,8).value ">=111", or "<=170" then
next I
else
repeat loop
end if

not sure the proper syntax but something like that maybe?

I want it to repeat the code until the condition is met

[Keebellah]

Here's you file again.
How about explaining it in a way others may understand?
1. What are you selecting or doing with the drop-down lists.
2. When you press the ENTER button? What happens.
3. What Loop.
I'm sure (and hope) YOU understand what you're asking, now only make it clear for the reader trying to help.

[UltimateNeo]

1. CboxHot Is selecting a named range AllNumbers or PartNumbers. The CboxOdd is selecting how many Odd numbers will be in the array. The Array can be 1 - 8 numbers long. for example (1),(23),(21),(35),(15) and so on depending on how many the Cboxodd.value is.

In H2 or cells(i,8) is the sum of 6 numbers that have been randomly selected and added up. With this number I want to be between >=111 and <=170 if not repeat the selection process until condition is met.
as I am selecting 6 numbers per row starting at row 5 then finishing at row 28, the code needs to repeat for all rows

If possible although might do with conditional formatting I want to change the background color to say green if the cell (H5) value is >=111 <=140 and then a gradation of that for the cell (H5) for if the value is >=141 <=170

2. When you hit the enter key you should get the random selected cells pasted into B5:G29 each row will not have a repeated selection. so 33 cant appear twice in the same row. Column H sums up B5:G5 all the way down to B29:G29

3. If you select TuesOn the same thing happens bu column H is Hidden and I becomes the seventh number and Column J becomes the sum column Sum(B5:G5,I5)

For tuesOn the sum will be different from the >=111 <=170 criteria

Hope that explains it better

[Keebellah]

We'll see, will read and re-read to see if I understand.