Shade alternate VISIBLE rows only

[mlynch]

Does anyone out there know how to do this, it's driving me mad!

I can get the shade alternate lines bit bit when I turn on my filter
the hiden rows are shaded the result gives shaded rows which appears
random.

I need only the VISIBLE rows (after the filter has been applied)
to have alternate shading.


e-mail (e-mail address removed)


many thanks

 

[Debra Dalgleish]

You can do this if you add a helper column to the table:

1. Insert a blank column (column A in this example)
2. Add a heading, e.g. Count
3. In row 2, enter the following formula, where column B
contains no blank cells within the table range:
=SUBTOTAL(3,$B$2:$B2)
4. Copy the formula down to all rows of data
5. Select the worksheet
6. Choose Format>Conditional Formatting
7. Choose Formula Is
8. In the formula box, type:
=AND($A1<>"",MOD($A1,2)=0)
9. Click the Format button, and select a colour for the shaded rows
10. Click OK, click OK

Filter the table, the count will change, and alternate rows will be shaded.

 

[JMay]

Cool Debra; One additional thing I like to add to the CF formula statement
is: Expl
Column()<=4 << to have it only "color" columns A, versus it running the
length of the row to IV?.

=AND($A1<>"",Column()<=4,MOD($A1,2)=0)

HTH
JMay

 

[Dave Peterson]

Maybe applying the CF to just A (B:E if A is hidden???) would be easier.

Cool Debra; One additional thing I like to add to the CF formula statement
is: Expl
Column()<=4 << to have it only "color" columns A, versus it running the
length of the row to IV?.

=AND($A1<>"",Column()<=4,MOD($A1,2)=0)

HTH
JMay

 

[sheryl]

i'd love to see this myself

 

[Dave Peterson]

Debra Dalgleish gave an answer:

http://google.com/[email protected]

 

[David Turner]

sheryl wrote

i'd love to see this myself

VBA solution provided to me by Myrna Larson just yesterday:
-------Quote
I've written two routines, one to color the rows, the other to remove the
color. I picked a
light gray for the shaded rows. Pick another color index value if you
like by changing the
number 40 in this line

CI(1) = 40

to some another value.


Sub FaxIt()
Columns("A:A").AutoFilter Field:=1, Criteria1:="<>"
ColorRows '<<<<<
Application.ActivePrinter = "Brother PC-FAX on BMFC:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Brother PC-FAX on BMFC:", Collate:=True
RemoveColor '<<<<<
Columns("A:A").AutoFilter
End Sub


Sub ColorRows()
Dim c As Range
Dim CI(0 To 1) As Long
Dim i As Long
Dim Rng As Range

CI(0) = xlColorIndexNone
CI(1) = 15 'I actually changed this from her's for light grey
i = 0

Set Rng = ActiveSheet.UsedRange
Rng.Interior.ColorIndex = CI(i)

For Each c In Rng.Columns("A").SpecialCells(xlCellTypeVisible)
Rng.Rows(c.Row).Interior.ColorIndex = CI(i)
i = 1 - i
Next c
End Sub

Sub RemoveColor()
ActiveSheet.UsedRange.Interior.ColorIndex = xlColorIndexNone
End Sub
-------EndQuote

 

[mlynch]

Thank you to Debra Dalgleish and Dave Peterson for helping me find a
solution to this problem. I would never have thought to do it that
way.

It works just fine

Saved me a lot of time and effort!

 

[David McRitchie]

Hi Sheryl,
"Me too" type replies are not very useful. They detract from
someone answering who does know the answer. Fortunately
for the poster Debra replied first before your reply came up,
though she might have looked anyway at your reply just to see
to make sure the question was answered correctly. That why
I looked here. A graphic example can be seen in
http://www.mvps.org/dmcritchie/excel/sumdata.htm

Actually my solution is different (not that it matters) :
Conditional Format: =MOD(SUBTOTAL(3,$C1$2),3)=0
Shows color banding working with/without Filtering. Every third row.

It is possible to help someone arrive at a solution without knowing the
specific answer, in fact a group effort has resulted in many answers that
it is doubtful that any of the participants could have answered by themselves.