[Excel] Writing a macro to set alternate background colors in rows?

[anhn]

Do you think this post should be in CodeBank?

1. Method 1: Hard-color alternate rows
This code will need to be re-run if later a row will be inserted or deleted within the range.

Sub AlternateRowColors(Optional aRange As Range, _
                       Optional BG0 As Long = 19, _
                       Optional BG1 As Long = 20)
   Dim BG As Variant, Area As Range, r As Long

   BG = Array(BG0, BG1) ' use 2 colorindex of your choices
   If aRange Is Nothing Then Set aRange = Selection
   '-- we need to use Areas because aRange or Selection may consist of
   '-- more than one areas, otherwise only the first area will be colored
   For Each Area In aRange.Areas
      For r = 1 To Area.Rows.Count
         Area.Rows(r).Interior.ColorIndex = BG(r Mod 2)
      Next
   Next
End Sub

Sub UsageExamples()
'-- Clear all interior color
   [A1:K20].Interior.ColorIndex = xlNone
   
'-- Example 1:
   [A4:C10].Select
   AlternateRowColors
   MsgBox "[A4:C10] was selected and colored as default"
   
'-- Example 2:
   [B6:E12].Select
   AlternateRowColors , 15, xlNone
   MsgBox "[B6:E12] was selected and colored with my choices"

'-- Example 3:
   AlternateRowColors [H1:K20]
   MsgBox "[H1:K20] was colored as default without selected"

'-- Example 4:
   AlternateRowColors [A1:D10,D8:F15], 18, 23
   MsgBox "2 Areas [A1:D10] and [D8:F15] was colored with my choices"
   
   Range("A1").Select
   
End Sub

2. Method 2: Use Conditional Formating to color alternate rows
The advantage of Conditional formatting method here is when you insert a new row or delete a row within the range then you don't need to re-run the code, the colors will be auto adjusted.

Sub ConditionalAltRowColors()
   '-- No need to use areas here even the Selection consist of more than one areas.
   '-- You can replace Selection with any range of your choices.
   With Selection.FormatConditions
      .Delete
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
      .Item(1).Interior.ColorIndex = 19
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
      .Item(2).Interior.ColorIndex = 20
   End With
End Sub

PS.
No need to say thanks to my code if it works for you, but if it is used then it should be credited.
Rows(i) instead of Rows(i & ":" & i) was what I taught someone when he was still young.