The Dead Horse: Alternating Color rows REGARDLESS of hidden, autofiltered, etc Please help

[chriscovino]

Hi, All, I am new to THIS forum, however, please know that I am of the age that I am familar with Message Board Etiquette, and after three hours of perusing the Internet and this Forum, I have not found a concise answer to my question, which seems to have been asked 4,393,778 times, with many tiny variations and almost as many responses; I am going to make this quite clear hopefully, and hopefully someone can give me the answer. Again, Thank you SO much and know that I DID do my due diligence searching EVERYWHERE for this answer, to no avail.

I am using Excel 2008 (Mac) (at work, not my choice).
My Current Project is a 2500+ Row Worksheet, with about 300 Columns.
Pivot Table isn't the best option for me as I am not summing or totalling anything.. I do use AutoFilter a LOT
ALL I WANT IS THIS... I want the rows to alternate colors REGARDLESS of the columns I've hidden, Autofilters I've used, Columns I've moved...
I just want the screen I am looking at to alternate colors, to make it easier to peruse the data and keep track of which record I'm viewing.
I've tried the ROW function, didn't work when i autofiltered. I tried the SUBTOTAL function, also it did not work.
All I want is alternating colored rows, no matter what i do to the data.
Please help me; This shouldn't be this difficult.
thank you again; have a super day.
Chris Covino

[Jonmo1]

Not familiar with Mac versions of XL, but in Windows version XL2007+ you can use the Table Feature.

Highlight ALL the data
On the Insert Tab, click Table - then click OK

[ben_hensel]

Whenever I need to do something like this (usually on a 50x500 table exported from Access), I generally just apply an automatic table style to the data and pick one of the "medium" color styles. If you're already using filtering etc this is probably not going to cause problems.

[Ron Coderre]

I was going to suggest converting your data list to an Excel Table because tables adjust banded rows to accommodate filtered data...but, discovered that Excel 2008 does not have that exact feature.
However, I found this blurb in a post at this website: https://answers.yahoo.com/question/i...3175026AAjkcPp

"Click anywhere in the data and then from the View menu choose View > Toolbars > List. That opens a new toolbar called the List manager. Click the First button: List Wizard to start the wizard that will help you format your data into a list (when opened in Windows or Excel 2011 on a Mac it will become a Table) "

Does that help?

[chriscovino]

I had tried a list because it was a feature I found that had an option for alternating row colors, my first attempt didn't go very well, I have column headers in Row 1 that I've frozen so I can see it as I scroll; I selected the "Column Header" button when creating the list, and it seemed to ignore the column header and put autofilter buttons on the first row, which was actually the first row with data in it...
One other thing that I failed to mention, is that out of the 300 columns in my sheet; the first 100 are fixed data, meaning I am not manipulating it at all (adding, changing, removing); The second 200 columns, they start out blank, and i will be adding things to practically every one of the Rows whether it be 5 cells or 60... When I tried doing the ROW/SUBTOTAL functions, I set the range to be the fixed data, and I left the back part of the sheet a solid color; I don't know if a list will work with all of the columns and that I have to add data to cells after sorting multiple times to find what I'm looking for?

[Andy Pope]

This works for me using a helper SUBTOTAL column

[daddylonglegs]

Hello Chris,

Let's assume that you have data in the range A2:Z1000 where A2 down is fully populated (if that isn't the case for column A then you need to pick another column that's fully populated)

Select that whole range and use this formula in conditional formatting

=mod(subtotal(3,$a$2:$a2),2)=0

apply required format

Then also add another condition with this formula

=mod(subtotal(3,$a$2:$a2),2)=1

and add the other colour required

This should colour alternate visible data when filtering

[chriscovino]

if I am OK with just selecting 1 color and the other being white, do I have to add the second formula??
I really just want to have a little something to help me when I am trying to stay on a line (row) and keep losing my place!!

[coolblue]

if I am OK with just selecting 1 color and the other being white, do I have to add the second formula??
I really just want to have a little something to help me when I am trying to stay on a line (row) and keep losing my place!!

Not sure who your question is directed to but, you should be able to do it with subtotal(10?, ...) and conditional formatting. Should not have to use vba.


Sent from my iPad using Tapatalk

[ranman256]

Sub ColorAltLines()
Dim bOn As Boolean
Rows("1:1").Select
BlueTint

Range("A2").Select
While ActiveCell.Value <> ""
   If bOn Then
      Rows(ActiveCell.Row & ":" & ActiveCell.Row).Select
      OrangeTint
   End If
   
   bOn = Not bOn
   ActiveCell.Offset(1, 0).Select   'NEXT ROW
Wend
End Sub

Private Sub OrangeTint()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
End Sub

Private Sub BlueTint()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
End Sub

[chriscovino]

YIKES!! I thought i was learning an advanced feature of Excel, but Good Grief, this looks like a program to reverse the rotation of the Solar System!!!
(that was my attempt at humor, not to put down your OMG amazing ability to write code!)...
so is this 20 line code, the "CONDITIONAL FORMATTING?" where would that go?

[coolblue]

Hi, what have you tried with the subtotal function? You should be able to do what you want by adding a helper column with =Subtotal(103,x) in it, where x is a non-empty cell on the same row. You can then add another helper column with a running total on the subtotal columns. This will only increment on visible rows, so you end up with a running total of visible rows. You then just add a conditional formatting rule with one colour for even and one for odd based on the running total helper column.


Sent from my iPad using Tapatalk

[chriscovino]

Firstly, let me just say, that I SO appreciate everyone's willingness to help, especially for a question that is so prevalent across the web and every message board from Yahoo to Ask, to specialized message boards. I am appreciative that no one flamed me; hopefully it is because I did mention (and it's true) that I spent over three hours researching this.
Secondly, I, at least on initial impression, FOUND THE BEST RESPONSE FOR MY SITUATION!!
Before I reveal the Winner, Please allow me to award some Honorable Mentions...
RANMAN, you need an "I" in your name, because you are like RAIN MAN with your ability to program code, or whatever that is; and I'm confident that if I knew where to put the code, it would work like a champ. I tried a much easier response and it worked, so we'll never know, but dude, thank you. that's crazy that a spreadsheet would need such programming just to change a color..!!!
Rod and Andy, for doing a little digging around and posting a link to a web answer or an XLSX, thank you. the "Above and Beyond" concept definitely is in play.

and now, the winner.. Oh no, wait.. first...

If any of you here are involved with Excel like from a creation standpoint, I would just like to offer... I know I'm on a relatively older version of Excel, but for the love of Christmas.. all I wanted was to always see my spreadsheet alternating colors no matter how I manipulated my data. I didn't want to add three columns, put a "helper" column (not that I am condemning the suggestion at all, because it's a working solution; I just believe it shouldn't be that complicated to do something so simple in principle.) add 42 lines of C++... As I get more advanced in Excel, I see how awesome it is, and how Excel can do some ridiculously tricky stuff, but then there are things and concepts so elementary and fundamental in concept that either aren't an inbred part of the program, or requires so much extra tasks to make it happen, that it sometimes becomes not worth it. Just my two cents.

and Now.. .Giving DaddyLongLegs his due....
DDL, I saw the MOD formula, and have tried probably eight different ones that I found across the WWW, and for some reason, while I could get my unfiltered sheet alternated, when I started filtering and filtering (x6 sometimes), it would invariably FAIL...
my initial inspection of =mod(subtotal(3,$a$2:$a2),2)=0 is that it is exactly what I was looking for; i've applied filters quickly, moved columns, and it all seems to stay alternated.. I think maybe the problem was my first column had maybe 3 cells of data and 2500+ empty ones; I added a mere "." to the cell, andnow everything seem to work great.
You all were so quick and thorough to offer assistance, and I hope you feel that my wordy, rambling post is commensurate to your efforts in showing my Thanks; I do appreciate you and hope you won't mind if I BM this site to come back when I have future issues (that I can't find a working answer in TechnoSpace).
Good Day, All.
Chris Covino, Madison WI. USA.

[Jonmo1]

I think maybe the problem was my first column had maybe 3 cells of data and 2500+ empty ones; I added a mere "." to the cell, andnow everything seem to work great.

Rather than adding a bunch of .'s to all your blanks in column A
I'd suggest changing the range subtotal refers to to a column that actually has data in every row
If you don't have a single column that actually contains data in every row....then Make one.
Even if you just populate that column with x's. You can hide that column.

[chriscovino]

The first column I added because as I am researching the data, I often have questions that I will FW to the experts (much like yourselves); I want the column there so that whenever I have a question, i can just fill it in and at the end of the week, "Show NonBlanks" and there are my questions..
However, as I was typing this, I realized the other part of what you said, if I inserted a column to the left of my Notes/Questions column, populated THAT with an X or a period, then did the formula, and hid THAT row, I could keep the integrity of my mostly blank Notes Column!!... JONMO, add yourself to the Honorable Mention List!!