Please help - coloring rows according to the number of names in column A2: to the end

[K0st4din]

Hello friends,
I searched the internet, but I only got to the "design" menu in an artwork that could stain my rows in one, such as gray, white, gray, white etc or in the options offered.
I'm looking, I'm probably going to a macro - for a way that in column A2:J by the end of the table I highlight the lines in range A2:J to the end of the table, according to the names in column A2:A in gray and neutral (white, standard for the table).
And because the thing I do not think of how to get done will be also used in MAC and in 2007, 2010, 2013.
I will set an example, and if you have any questions, please ask me to try to clarify more.

[/URL][/IMG]

 

[Yongle]

Are you asking for this?

When the value in column A changes then if this row is "white" the next row with different value is "grey"
When the value in column A changes again then if this row is "grey" the next row with different value is "white"

 

[Yongle]

Try this
- code triggers whenever value in colum A changes
- assumes headers in row1 and data starts in A2

Put code in SHEET module (right-click on sheet tab \ View Code \ paste code in window on right)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long, cel As Range, rng As Range, cel2 As Range
    Dim c1 As String:       c1 = 16777215
    Dim c2 As String:       c2 = 500000
    If Not Intersect(Columns("A"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Range("A2").Interior.Color = c1
        r = Range("A" & Rows.Count).End(xlUp).Row - 2
            For Each cel In Range("A3").Resize(r)
                Set cel2 = cel.Offset(-1)
                Set rng = cel.Resize(, 10)
                Select Case cel.Value = cel2.Value
                    Case True:  rng.Interior.Color = cel2.Interior.Color
                    Case Else:  If cel2.Interior.Color = c1 Then rng.Interior.Color = c2 Else rng.Interior.Color = c1
                End Select
            Next cel
        Application.ScreenUpdating = True
    End If
End Sub

 

[Peter_SSs]

I don't use a Mac but if you are looking for a solution using Excel's built-in Conditional Formatting, you might be able to adapt this.

 

[K0st4din]

Try this
- code triggers whenever value in colum A changes
- assumes headers in row1 and data starts in A2

Put code in SHEET module (right-click on sheet tab \ View Code \ paste code in window on right)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long, cel As Range, rng As Range, cel2 As Range
    Dim c1 As String:       c1 = 16777215
    Dim c2 As String:       c2 = 500000
    If Not Intersect(Columns("A"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Range("A2").Interior.Color = c1
        r = Range("A" & Rows.Count).End(xlUp).Row - 2
            For Each cel In Range("A3").Resize(r)
                Set cel2 = cel.Offset(-1)
                Set rng = cel.Resize(, 10)
                Select Case cel.Value = cel2.Value
                    Case True:  rng.Interior.Color = cel2.Interior.Color
                    Case Else:  If cel2.Interior.Color = c1 Then rng.Interior.Color = c2 Else rng.Interior.Color = c1
                End Select
            Next cel
        Application.ScreenUpdating = True
    End If
End Sub

Hello, thank you very much.
That's what I was trying to do with my experiments and search on the internet.
It's just brilliant.

I don't use a Mac but if you are looking for a solution using Excel's built-in Conditional Formatting, you might be able to adapt this.

Hello,
I'll try it out when I go to the computer boss's office.
It only bothers me how to modulate it because I need a range from A to J to get colored. But I will try.
Thank you very much to both of you.
I wish you good health.
See you soon

 

[Peter_SSs]

It only bothers me how to modulate it because I need a range from A to J to get colored. But I will try.

Just put those helper column formulas in column K instead of column B and then the Conditional Formatting formulas will be
=$K2=-1
=$K2=1