Excel VBA - color/shade alternating rows in filtered list

robertvdb

New Member
Joined
Jan 10, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
I have a filtered list, and I need to shade the alternating rows in this list.

Can anyone help in VBA ?

Thanks in advance.
 

Attachments

  • filtered.jpg
    filtered.jpg
    154.8 KB · Views: 8

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,188
Office Version
  1. 2016
Platform
  1. Windows
Any reason you need to do with VBA?
Can be done with conditional formatting - easily available on the internet.
 

robertvdb

New Member
Joined
Jan 10, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Thanks footoo, but it's part of a 100% VBA project, so I need the VBA. The end user is not able to apply conditional formatting.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,188
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub vv()
With Intersect(Range("A1:A" & Cells(Rows.Count, 1).End(3).Row).EntireRow, ActiveSheet.UsedRange)
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(SUBTOTAL(3,$A$1:$A1),2)=0"
    .FormatConditions(1).Interior.ColorIndex = 37
End With
End Sub

Above assumes there are no blank cells in column A.
 
Solution

robertvdb

New Member
Joined
Jan 10, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks footoo but this will not work, since the row numbers are not a natural serie 1,2,3,4,5 etc.

What I need is kind of a loop which starts at the first row, then shades this first row, then shades the row 2 lines below, etc until the last row.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,188
Office Version
  1. 2016
Platform
  1. Windows
Thanks footoo but this will not work, since the row numbers are not a natural serie 1,2,3,4,5 etc.
The row numbers are irrelevant.
The only caveat : there must be no blanks in column A.
Why don't you try it?
 

robertvdb

New Member
Joined
Jan 10, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Thanks ! this seems to work. All I still want to do is to better define the range --> from the top row until the last row.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,188
Office Version
  1. 2016
Platform
  1. Windows
All I still want to do is to better define the range --> from the top row until the last row.
That is what the code does based on column A.
Is there a problem?

If the code is based on a loop, it would probably be quite slow :
VBA Code:
Sub v()
Dim rng As Range, lc&, x&, cel As Range
Set rng = Range("A2:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible)
Cells.Interior.ColorIndex = xlNone
lc = Cells(1, Columns.Count).End(xlToLeft).Column
x = 1
For Each cel In rng
    If x Mod 2 = 0 Then Range(cel, Cells(cel.Row, lc)).Interior.ColorIndex = 37
    x = x + 1
Next
End Sub
 

robertvdb

New Member
Joined
Jan 10, 2021
Messages
12
Office Version
  1. 2010
Platform
  1. Windows
Thanks footoo ! Problem solved. I appreciate your assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,349
Messages
5,601,119
Members
414,429
Latest member
Bilaal xaka

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