Filling Only Visibile Odd Rows with Colour

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Filling Only Visibile Odd Rows with Colour

Post by kpark91 »

Hi, as the title suggests, I want to fill odd rows with colour.
But here is the catch for only visible rows (as I have autofilter in place, which hides, not delete, the rows accordingly)

So far, I have code for filling in odd rows or filling in rows alternatingly(?) with colour.

Code: Select all

Sub FillOnlyOddRows()

    Application.ScreenUpdating = False

    For c = 2 To Range("A" & Rows.count).End(xlUp).Row
        If c Mod 2 = 1 Then
            Rows(c).EntireRow.Interior.Color = RGB(220, 246, 255)
        Else
            Rows(c).EntireRow.Interior.Color = RGB(255, 255, 255)
        End If
    Next c

    Application.ScreenUpdating = True

End Sub
This is for a database which I have to somehow organize and I'm trying to make it look 'pretty' by colouring alternating rows.
I don't have one

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filling Only Visibile Odd Rows with Colour

Post by HansV »

The disadvantage of this method is that the coloring will become messed up when the user changes the filter. You can use conditional formatting to color the filtered rows:

Excel 2003 or earlier:
- Select the entire range (starting in row 2).
- Select Format | Conditional Formatting...
- Select Formula Is from the first dropdown.
- Enter the following formula in the box next to it:

=MOD(SUBTOTAL(3,$A$2:$A2),2)

- Click Format...
- Specify the desired fill color.
- OK your way out.

Excel 2007 or later:
- Select the entire range (starting in row 2).
- Activate the Home tab of the ribbon.
- Click Conditional Formatting.
- Select New Rule...
- Select 'Use a formula to determine which rows to format'.
- Enter the following formula in the box:

=MOD(SUBTOTAL(3,$A$2:$A2),2)

- Click Format...
- Specify the desired fill color.
- OK your way out.

The coloring will be adjusted automatically when the user changes the filter.
Best wishes,
Hans

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Re: Filling Only Visibile Odd Rows with Colour

Post by kpark91 »

Wow.I didn't realize it can be easily done without VBA.

Thank you very much. HansV.

I see you're online again, admin. :P
I don't have one

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filling Only Visibile Odd Rows with Colour

Post by HansV »

Conditional formatting is very powerful, especially with the ability to use formulas.
Best wishes,
Hans

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Re: Filling Only Visibile Odd Rows with Colour

Post by kpark91 »

Thank you for the response but Hold on... It doesn't work :(

I'm using Excel 2007 but saving it as Excel 97-03 Document due to compatibility issues within the company.

In case, I confused you, it's not working at all when a value is filtered out.

I've tried using
=MOD(SUBTOTAL(3,$A$2:$BK733),2)
=MOD(SUBTOTAL(103,$A$2:$BK733),2) 'To Ignore Hidden Cells

However, with no luck :'(

Moreover, when using this conditional formatting, the whole workbook was slowed down in terms of scrolling, keyboard translation movements, which is a critical issue.

I'll show you the whole VBA code of the filling odd rows (with hidden rows),

Code: Select all

Sub FillOnlyOddRows()

    Application.ScreenUpdating = False

    For c = 2 To Range("A" & Rows.count).End(xlUp).Row
        If c Mod 2 = 1 Then
            Rows(c).EntireRow.Interior.Color = RGB(220, 246, 255)
        Else
            Rows(c).EntireRow.Interior.Color = RGB(255, 255, 255)
        End If
    Next c

    Application.ScreenUpdating = True

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = 1 Then
        Call FillOnlyOddRows
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Call FillOnlyOddRows
End Sub
I would only run filling in odd rows when something is changed in the first row (that's where my autofilter is..)
It would work for sorting since there are no hidden rows.
but it wouldn't work for filtering out certain range of values (since there are hidden rows.)

Are there any ways to VB code it in manner of only filling color in visible rows alternatingly?

Thank you :)
I don't have one

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filling Only Visibile Odd Rows with Colour

Post by HansV »

It'd probably work if you used Excel 2003 to set it up. Unfortunately, compatibility between Excel 97-2003 and Excel 2007 (and later) is far from perfect.

Try this version of FillOnlyOddRows:

Code: Select all

Sub FillOnlyOddRows()
    Dim n As Long
    Dim c As Long
    Application.ScreenUpdating = False
    For c = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Rows(c).Hidden = False Then
            n = n + 1
            If n Mod 2 = 1 Then
                Rows(c).Interior.Color = RGB(220, 246, 255)
            Else
                Rows(c).Interior.Color = vbWhite
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Re: Filling Only Visibile Odd Rows with Colour

Post by kpark91 »

Interesting...
Thank you for your code but
I've tried your code but it works but only when either sorting with filter, clicking on row 1 but when I autofilter it doesn't automatically color it for me.
It would just stay the same but when I click the first row, then it colors for me.

So, I've added Range("A734").Value = 1 at the end of the FillOnlyOddRows sub
but no value would be shown if I try to leave/put in some range of values with autofilter.
It seems to not accept it as a selection change when filtering out some values... (maybe this is a bug we should report about :D)

I want this to be as fully automated as possible and I thought it was such a simple task... :(
I don't have one

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filling Only Visibile Odd Rows with Colour

Post by HansV »

Scrap the Worksheet_Change and Worksheet_SelectionChange event procedures.

Make sure that there is at least one formula on your worksheet that refers to the filtered range, e.g. =SUBTOTAL(9,A2:A1000)

Call FillOnlyOddRows from the Worksheet_Calculate event procedure in the worksheet module.
Filtering causes the subtotal formula to be recalculated, and this calls FillOnlyOddRows.

Code: Select all

Private Sub Worksheet_Calculate()
  Call FillOnlyOddRows
End Sub
Just like the conditional formatting, this may have a negative impact on performance, since it'll occur whenever the sheet is recalculated.
Best wishes,
Hans

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Re: Filling Only Visibile Odd Rows with Colour

Post by kpark91 »

Wow! You did it!!

I never knew it could be so easy -_-

but Worksheet_Calculate event calling FillOnlyOddRows didn't have any negative impact on my performance which is even better!
With the Conditional Formatting, I don't know why but it was very slow in scrolling and keyboard translation movements!

Thank you very much HansV!
I don't have one