colorindex number randomize

[james potter]

Hello,

I would like to make the colorindex number random but I have a problem with the code. Maybe someone can help me?

I have tried it with for ... next argument but that keeps running until the last number. With no use of for ... next argument if works fine but cell color and indexnumber stays the same.

Sub looping2() 

Range("a2").Select 

'for i = 15 to 50

    Do 
    If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then 
    Selection.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex 
    Else 
    Selection.Interior.ColorIndex = 13 'i
    End If 
    ActiveCell.Offset(1, 0).Select
    'Next i
    Loop Until IsEmpty(ActiveCell) 

End Sub

 

[Von Pookie]

Well, I think perhaps the cell color is staying the same since, if the values match, you're assigning the color of the previous cell. For example, you are starting with A2. If the value of A2 is the same as A1, and A1 contains no interior color, A2 will not have a color, either. Then if A3=A2, again, no color and it can continue all the way down the column.

Here's an example that simply colors the cells in column A randomly. This does not take into account the cell values, it only colors:

Sub looping2()
Dim i As Integer
Dim xRow As Long, LastRow As Long

' find number of the last used row after A2
' (stops when it hits a blank cell)
LastRow = Range("A2").End(xlDown).Row

'From VBA help file on Rnd function:
' Before calling Rnd, use the Randomize statement
' without an argument to initialize the random-number
' generator with a seed based on the system timer.
' To produce random integers in a given range, use this formula:
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

'loop through the cells in the range
For xRow = 2 To LastRow
    ' determine the colorindex (between 15 and 50)
    Randomize
    i = Int((50 - 15 + 1) * Rnd + 15)
    
    ' color the cell
    Cells(xRow, 1).Interior.ColorIndex = i
Next xRow

End Sub

 

[james potter]

I have put some different values in column A. Therefore each cell that differs from the previous one will be made noticable with a color. But each time it takes the same color. In my code the color purple. I would to randomise it.

 

[Norie]

James

What are you actually trying to do?

I don't see anything random in the code you posted.

 

[Von Pookie]

I have put some different values in column A. Therefore each cell that differs from the previous one will be made noticable with a color. But each time it takes the same color. In my code the color purple. I would to randomise it.

Yes, I gathered that. Did you even look what I posted? It randomizes the colors using the range you were trying (colorindex 15 to 50). There may be instances where the same color is used twice in a row, but it should not be the same color for all of the cells.

Edit: The sample code I posted does not take the cell values into account, which I mentioned, but it should serve as some sort of an example of at least one way to randomize the colors.

Norie: Read it again. He has nothing in his code about randomizing because he doesn't know how. That's what he's asking.

 

[james potter]

I am sorry... due to a deadline I have looked briefly to the input of von pookie. Today without the deadline I've got the code working in 10 minutes. Thank you all for your support. For the record here is the code I use now.

Sub looping2()

Application.ScreenUpdating = False

Dim noSheets
Dim x

noSheets = ActiveWorkbook.Worksheets.Count
For x = 1 To noSheets

Sheets(x).Activate
Range("a2").Select

    Do
    If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
    ActiveCell.EntireRow.Select
    Selection.Interior.ColorIndex = ActiveCell.Offset(-1, 0).Interior.ColorIndex
    Else
    i = Int((50 - 15 + 1) * Rnd + 15)
    ActiveCell.EntireRow.Select
    Selection.Interior.ColorIndex = i
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)
    
Next x

Application.ScreenUpdating = True

Sheets(1).Activate

End Sub