Counting cells

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Is the red interior generated from Conditional Formating, if so what are you using Formula/Cell Value??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
On 2002-04-05 03:56, verluc wrote:
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

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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top