Use VBA to shade every 3 rows (banding)
 

I've seen ways to do this with conditional formatting (http://
www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

Selection.Interior.ColorIndex = xlNone

Dim Counter As Integer

For Counter = 1 To Selection.Rows.Count
If Counter Mod 2 = 0 Then
Selection.Rows(Counter).Interior.ColorIndex = 54
End If
Next
End Sub

Use VBA to shade every 3 rows (banding)
 

replace

If Counter Mod 2 = 0 Then

with

If Counter Mod 3 = 0 Then



On 2 Cze, 13:48, MikeS wrote:
I've seen ways to do this with conditional formatting (http://www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

* * Selection.Interior.ColorIndex = xlNone

* * Dim Counter As Integer

* * For Counter = 1 To Selection.Rows.Count
* * * * If Counter Mod 2 = 0 Then
* * * * * * Selection.Rows(Counter).Interior.ColorIndex = 54
* * * * End If
* * Next
End Sub

Use VBA to shade every 3 rows (banding)
 

On Jun 2, 7:56*am, Jarek Kujawa wrote:
replace

* * * * If Counter Mod 2 = 0 Then

with

* * * * If Counter Mod 3 = 0 Then

On 2 Cze, 13:48, MikeS wrote:

I've seen ways to do this with conditional formatting (http://www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

* * Selection.Interior.ColorIndex = xlNone

* * Dim Counter As Integer

* * For Counter = 1 To Selection.Rows.Count
* * * * If Counter Mod 2 = 0 Then
* * * * * * Selection.Rows(Counter).Interior.ColorIndex = 54
* * * * End If
* * Next
End Sub



that's not quite what I'm looking for. Let me explain better.

If I select a group of cells (A1:M12), I want:

rows 1-3 = no shade
rows 4-6 = shaded
rows 7-9 = no shade
rows 10-12 = shaded

thanks!

Use VBA to shade every 3 rows (banding)
 

pls click YES if it helped

Sub HighlightAltRows()

Selection.Interior.ColorIndex = xlNone

Dim Counter As Integer

For Counter = 1 To Selection.Rows.Count Step 3
If Counter Mod 2 = 0 Then
Range(Selection.Rows(Counter), Selection.Rows(Counter +
2)).Interior.ColorIndex = 54
End If
Next
End Sub


On 2 Cze, 14:12, MikeS wrote:
On Jun 2, 7:56*am, Jarek Kujawa wrote:





replace

* * * * If Counter Mod 2 = 0 Then

with

* * * * If Counter Mod 3 = 0 Then

On 2 Cze, 13:48, MikeS wrote:

I've seen ways to do this with conditional formatting (http://www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

* * Selection.Interior.ColorIndex = xlNone

* * Dim Counter As Integer

* * For Counter = 1 To Selection.Rows.Count
* * * * If Counter Mod 2 = 0 Then
* * * * * * Selection.Rows(Counter).Interior.ColorIndex = 54
* * * * End If
* * Next
End Sub

that's not quite what I'm looking for. *Let me explain better.

If I select a group of cells (A1:M12), I want:

rows 1-3 = no shade
rows 4-6 = shaded
rows 7-9 = no shade
rows 10-12 = shaded

thanks!- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

Use VBA to shade every 3 rows (banding)
 

'Replace the If condition with
If (Counter + 2) \ 3 Mod 2 = 0 Then


If this post helps click Yes
---------------
Jacob Skaria


"MikeS" wrote:

On Jun 2, 7:56 am, Jarek Kujawa wrote:
replace

If Counter Mod 2 = 0 Then

with

If Counter Mod 3 = 0 Then

On 2 Cze, 13:48, MikeS wrote:

I've seen ways to do this with conditional formatting (http://www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

Selection.Interior.ColorIndex = xlNone

Dim Counter As Integer

For Counter = 1 To Selection.Rows.Count
If Counter Mod 2 = 0 Then
Selection.Rows(Counter).Interior.ColorIndex = 54
End If
Next
End Sub



that's not quite what I'm looking for. Let me explain better.

If I select a group of cells (A1:M12), I want:

rows 1-3 = no shade
rows 4-6 = shaded
rows 7-9 = no shade
rows 10-12 = shaded

thanks!

Use VBA to shade every 3 rows (banding)
 

On Jun 2, 8:22*am, Jarek Kujawa wrote:
pls click YES if it helped

Sub HighlightAltRows()

* * Selection.Interior.ColorIndex = xlNone

* * Dim Counter As Integer

* * For Counter = 1 To Selection.Rows.Count Step 3
* * * * If Counter Mod 2 = 0 Then
* * * * * * Range(Selection.Rows(Counter), Selection.Rows(Counter +
2)).Interior.ColorIndex = 54
* * * * End If
* * Next
End Sub

On 2 Cze, 14:12, MikeS wrote:

On Jun 2, 7:56*am, Jarek Kujawa wrote:

replace

* * * * If Counter Mod 2 = 0 Then

with

* * * * If Counter Mod 3 = 0 Then

On 2 Cze, 13:48, MikeS wrote:

I've seen ways to do this with conditional formatting (http://www.cpearson.com/excel/banding.aspx), but I'd like to do it with VBA.

How do I modify the below macro to shade alternate groups of 3 rows?
Instead of every other row...

Sub HighlightAltRows()

* * Selection.Interior.ColorIndex = xlNone

* * Dim Counter As Integer

* * For Counter = 1 To Selection.Rows.Count
* * * * If Counter Mod 2 = 0 Then
* * * * * * Selection.Rows(Counter).Interior.ColorIndex = 54
* * * * End If
* * Next
End Sub

that's not quite what I'm looking for. *Let me explain better.

If I select a group of cells (A1:M12), I want:

rows 1-3 = no shade
rows 4-6 = shaded
rows 7-9 = no shade
rows 10-12 = shaded

thanks!- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -



that worked! thanks!