If Then Else in a For Next loop

[oxwilder]

I'm trying to set the interior color of a cell as different than the cell above it unless the values are the same. I'm still learning VBA, and this site has been invaluable, but obviously I'm going to make a few mistakes.

Here's the code that I came up with to try to accomplish my task:

Please Login or Register  to view this content.

This results in a "Next without For" error. Can someone please gently explain what I'm doing wrong?

Thanks very much.

I'm just realizing that even if I didn't get that error, I would still always get a random color. Hrmph.

[mehmetcik]

You have started two If Then Else End If Statements.

But you have only closed one of them.

Please Login or Register  to view this content.

[oxwilder]

Well, that helps. It runs now, but each cell in the column is the same color. What I was hoping to do was assign a new color to a cell when the cell above had a different value, otherwise the color stays the same.

[guru008]

Change

Sub randomCellColor()

Dim lrow As Long
Dim color As Integer
Dim count As Long
color = 1


lrow = Cells(Rows.count, 1).End(xlUp).Row
color = Application.WorksheetFunction.RandBetween(1, 56)

For count = 2 To lrow 'excludes header row?
If Cells(count, 1).value <> Cells(count - 1, 1).value Then
Cells(count, 1).Interior.ColorIndex = color + 1
color = color + 1
Else
If Cells(count, 1).value = Cells(count - 1, 1).value Then
Cells(count, 1).Interior.ColorIndex = color
color = color + 1
End If
End If
Next count

End Sub

[oxwilder]

Ok, I think I've got it.

Please Login or Register  to view this content.

I needed to generate a new random number each time the loop executed or each cell would just be the same color value. Then I needed to say "If this cell value is the same as the one above it, make the colors the same."

Thanks for all your help once again

[oxwilder]

One more refinement: I found that--just by random--a cell would sometimes have a different value but the same color, which defeats the purpose of trying to assign unique colors to unique values. I fixed it by adding another nested if, and I don't know how clean it is, but it works.

Please Login or Register  to view this content.

I'm sure there's a simpler way to define an array and then pick an element at random, but I couldn't make any of the solutions I found apply. Anybody have any ideas?

Thanks everyone for getting me to this point. I'm finally starting to feel a little comfortable with all this!

[protonLeah]

Please Login or Register  to view this content.