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


  1. #1
    Board Regular
    Join Date
    Mar 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    I have a 8 column sheet with in the first columns a text.The other 7 columns are filled with numbers.
    Some of this cells have an "interiorcolor".
    On a row some have three cells colored,other rows have 5 colored and so on.All different.
    Perhaps there are rows with SEVEN colored cells.
    What I want to do is to write a macro that jumps by the "enter" to every row who have SEVEN colored cells?
    Is this possible?
    Who can help me.
    Many thanks

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Newark, Delaware
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    Try the following:

    Dim RowColors(8)
    Dim SevenColors(100)
    Dim NumRow, r, c As Long
    NumRow = Intersect(Columns(1), ActiveSheet.UsedRange).Rows.Count
    For r = 1 To NumRow
    For c = 2 To 8
    RowColors(c - 2) = Cells(r, c).Interior.ColorIndex
    Next c
    If RowColors(0) <> RowColors(1) And RowColors(0) <> RowColors(2) And RowColors(0) <> RowColors(3) _
    And RowColors(0) <> RowColors(4) And RowColors(0) <> RowColors(5) And RowColors(0) <> RowColors(6) _
    And RowColors(1) <> RowColors(2) And RowColors(1) <> RowColors(3) And RowColors(1) <> RowColors(4) _
    And RowColors(1) <> RowColors(5) And RowColors(1) <> RowColors(6) And RowColors(2) <> RowColors(3) _
    And RowColors(2) <> RowColors(4) And RowColors(2) <> RowColors(5) And RowColors(2) <> RowColors(6) _
    And RowColors(3) <> RowColors(4) And RowColors(3) <> RowColors(5) And RowColors(3) <> RowColors(6) _
    And RowColors(4) <> RowColors(5) And RowColors(4) <> RowColors(6) And RowColors(5) <> RowColors(6) _
    And RowColors(0) <> RowColors(7) And RowColors(1) <> RowColors(7) And RowColors(2) <> RowColors(7) _
    And RowColors(3) <> RowColors(7) And RowColors(4) <> RowColors(7) And RowColors(5) <> RowColors(7) _
    And RowColors(6) <> RowColors(7) Then
    MsgBox "Row " & r & " has seven unique interior colors"
    End If
    Next r

    I know its not pretty, but I couldn't think of another way to do it.
    Best regards,
    Allan Chara

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