Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shade even rows in a sheet | Excel Discussion (Misc queries) | |||
how to shade every other 5 rows | New Users to Excel | |||
shade hidden rows | Excel Discussion (Misc queries) | |||
shade rows | Excel Discussion (Misc queries) | |||
shade rows | Excel Discussion (Misc queries) |