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

K0st4din

Well-known Member
Joined
Feb 8, 2012
Messages
501
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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.
2018-08-23_08542881MeTrhwP1g6yuKXRSgp1535003714_thumb.jpg
[/URL][/IMG]
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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"
 
Upvote 0
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)
Code:
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
 
Upvote 0
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.
 
Upvote 0
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)
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

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