Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Counting cells

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is the red interior generated from Conditional Formating, if so what are you using Formula/Cell Value??
    "Have a good time......all the time"
    Ian Mac

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •