colorindex number randomize

james potter

New Member
Joined
Aug 15, 2005
Messages
45
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.

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

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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:

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

New Member
Joined
Aug 15, 2005
Messages
45
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
James

What are you actually trying to do?

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

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

New Member
Joined
Aug 15, 2005
Messages
45
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,362
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top