Counting cells

[verluc]

I have a sheet filled with numbers from A1:IA500
Some of those cells have a red interiorcolor.
If there are FIVE cells in a row with this red color,I want to change the color in yellow,if they are not FIVE the color stays red.
Can someone give me a macro to do this?
Thanks

[Ian Mac]

Is the red interior generated from Conditional Formating, if so what are you using Formula/Cell Value??

[Tom Urtis]

Please clarify 2 points:

Verify why those cells are red in the first place. Is it due to conditional formatting, or did someone actually format those cells' interior color index with a red shade?

What is the reason why those cells that are red should be red or yellow. Why does "5 in a row" make a difference...what is it about those 5, or any cells, that determine their color?

The goal here is to help us understand your spreadsheet better, which would help you in not receiving some unnecessary (and in this case potentially obnoxious) code, if conditional formatting can do the job instead.

[verluc]

On 2002-04-05 03:45, Ian Mac wrote:
Is the red interior generated from Conditional Formating, if so what are you using Formula/Cell Value??

No,it is generated from a macro: Sheets().Select
x = 5
While Cells(x, 1).Text <> ""
If Cells(x, 3).Value >= Cells(x, 4).Value Then
With Cells(x, 3)
.Interior.ColorIndex = 6
End With
Else
Range(Cells(x, 3), Cells(x, 256).End(xlToLeft)).Interior.ColorIndex = 2
End If
x = x + 1
Wend
End Sub

[Tom Urtis]

Sorry Ian, I posted the same thoughts after you...didn't see your's first.

[Ian Mac]

On 2002-04-05 03:56, verluc wrote:

No,it is generated from a macro: Sheets().Select
x = 5
While Cells(x, 1).Text <> ""
If Cells(x, 3).Value >= Cells(x, 4).Value Then
With Cells(x, 3)
.Interior.ColorIndex = 6
End With
Else
Range(Cells(x, 3), Cells(x, 256).End(xlToLeft)).Interior.ColorIndex = 2
End If
x = x + 1
Wend
End Sub

Why the macro?
I've not lookewd too hard at this but it seems to me your establishing cell contents and turning them 1 of 2 colours.
I think you may be able to do this with Conditional Formating.

if you want to send the book(or an eg) I can look at this in more detail,

alteratively post further details of the data you have with expected results, about 20-30 rows should easily suffice, and I'm fairly confident you'll get a solution from that.