Shading alternate rows that are visible

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a group of about 10 rows that are shaded every second row to aid in reading. My example below has no shading but it does on my spreadsheet.

This is what it looks like. I have added to row number at the start of the row so anyone knows where I am referring too. Obviously, the row number is not in my spreadsheet. The units will change and different rows will have numbers under units and other rows will have 0 at different times.



row19UnitsPrice ex. GST
row 20 - Total Hours - Weekday51$3,564.90
row 21 - Total Hours - Saturday0$0.00
row 22 - Total Hours - Sunday0$0.00
row 23 - Total Hours - Public Holidays0$0.00
row 24 - Total Nights - Sleepover3$525.00
row 25 - Total Nights - Accomodation Costs0$0.00
row 26 - Total Days - Activity Costs4$36.00
row 27 - Miscellaneous Day Total0$0.00
row 28 - Total Kms0$0.00

I have a procedure that runs and it hides the rows that have a 0 under the units so it the example above, the only rows that were not hidden are 20,24,26.

The procedure then prints the document to pdf after the rows have been hidden. In the above example, the output pdf shows the rows to not have any shading as the rows that are still visible are the alternate rows so they are white anyway.

Can someone please help me with vba to shade alternate rows that are visible.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks footoo, I have been trawling through results already but I have found nothing that is of use so I thought I would ask on here for some help.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Is there a formula that you can put in the conditional format part that will shade every second row but will also update if a row is hidden?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I found some code but I am not sure how to apply it to my situation. Can someone help me please?

VBA Code:
    Dim lShadedRow  As Long
    Dim lRowCounter As Long

    For lRowCounter = 1 To 15
        If Not Rows(Cells(lRowCounter, 1).Row).Hidden Then
            If lShadedRow > 0 Then
                lShadedRow = 0
            Else
                lShadedRow = lRowCounter
                Range(Cells(lRowCounter, "D"), Cells(lRowCounter, "F")).Interior.Color = vbGreen
            End If
        End If
    Next
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,148
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Found a fix, I converted the range to a table.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,647
Messages
5,626,075
Members
416,160
Latest member
SanbiVN

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
Top