Code or macro to stop calculation when meet criritie

[Thiyagu8423]

Hi
Anyone can help to provide code or suggestion ..currently im press F9 (Manual Calculation ) until meet critirie all the 7 coloum no duplicate value
Any vba code to generate macro , only stop when all coloum no duplicate value

​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​
​	​	​	​	​	​	​

 

[Cubist]

Your question is a bit unclear. It looks like you have conditional formatting for duplicate values in a column. What is the manual calculation doing? What do you want the VBA to stop doing when no duplicate value remains?

 

[Thiyagu8423]

hi sir , sorry yes my question is a bit unclear .
So I attached again the excel file.
Currently I set calculation option as "manual " and I press F9 a lot of time until meet criteria all the colour 1st set to 7 set their is no duplicate value

1st Set	2nd Set	3rd Set	4rd Set	5rd Set	6rd Set	7rd Set
8​	31​	13​	19​	24​	31​	36​
15​	17​	27​	32​	14​	23​	17​
15​	5​	27​	30​	12​	31​	31​
26​	18​	5​	12​	30​	30​	5​
11​	4​	32​	13​	32​	31​	15​
18​	18​	30​	20​	7​	8​	16​
1​	15​	18​	6​	20​	5​	13​
10​	21​	28​	19​	29​	15​	21​

Their is formula RANDBETWEEN(1,36) in all the cell and condition format to highlight colour when their is duplicate value
Any vba code i can use to run and stop when their is no duplicate value in each coloum .Currently im doing it manually F9 can its time consuming

 

[Cubist]

Try this on a copy.

Sub UniQueNumInColumns()
    Dim numbers() As Integer
    Dim totalCells As Integer
    Dim i As Integer
    Dim j As Integer
    Dim temp As Integer
    Dim rng As Range
    Dim r As Integer
    Dim c As Integer
    Dim numRows As Integer
    Dim numCols As Integer
    
    ' Set the range where you want to output the numbers
    Set rng = Range("A1:G8")

    numRows = rng.Rows.Count
    numCols = rng.Columns.Count
    totalCells = numRows * numCols
    
    ReDim numbers(1 To totalCells)
    For i = 1 To totalCells
        numbers(i) = i
    Next i
    
    For i = totalCells To 2 Step -1
        j = Application.WorksheetFunction.RandBetween(1, i)
        temp = numbers(i)
        numbers(i) = numbers(j)
        numbers(j) = temp
    Next i
    
    i = 1
    For c = 1 To numCols
        For r = 1 To numRows
            rng.Cells(r, c).Value = numbers(i)
            i = i + 1
        Next r
    Next c
End Sub