Best way to pass a collection of cell addresses to a sub?

[JenniferMurphy]

I am writing a macro to do some highlighting. The main macro calls several Subs to do some of the work. It needs to pass a list of cell addresses to these subs. I am wondering about the best way to do that.

My current solution is to pass them as separate arguments:

Sub Highlight()
   . . .
' Define the target addresses
Dim strTSTop As String
  strTSTop = Range("TSTop").Address
Dim strFLTop As String
  strFLTop = Range("FLTop").Address
Dim strAve As String
  strAve = Range("Ave").Address
   . . .
Call Fillem(StrTSTop, StrFLTop, strAve, FillColor)
   . . .
End Sub

Sub Fillem(Addr1 as string, Addr2 as string, Addr3 as string, FillColor as Long)
  . . .
Range(Addr1).Select
With Selection.Interior
  .Pattern = xlSolid
  .PatternColorIndex = FillColor
  .color = colorFill
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With

Range(Addr2).Select
With Selection.Interior
  .Pattern = xlSolid
  .PatternColorIndex = FillColor
  .color = colorFill
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With

Range(Addr2).Select
With Selection.Interior
  .Pattern = xlSolid
  .PatternColorIndex = FillColor
  .color = colorFill
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With

As I work on the sheet, the cells move around, new cells get added, and old ones get deleted. All of the cells are named, so the macro can access them using the names. That solves the problem of the cells moving around. But with the current implementation, if I add a cell, I have to edit both the call and the sub in addition to defining a new address variable.

I'd like to be able to pass a list of cell addresses ("D3, G3, M3, D10") so I only have to edit one variable and not have to change any other code. And I'd like to replace the repeated code in the Fillem sub with a loop.

Should I pass a comma-delimited string and use the Split function to break it up? Is there a better way?

Thanks

 

[RoryA]

Range("D3, G3, M3, D10") is a valid range so you wouldn't actually need to split that. The Range address argument is limited to 255 characters, so if you think you might exceed that I would suggest passing an array of strings and then looping through that since you seem to do the same formatting to every range. I would also note that there is no need to select the ranges to format them.

 

[PatOBrien198]

If, as you said, "All of the cells are named", then you can call the fillEm subroutine with the named range...

Call FillEm(Range("namedRangeOfCellsToFill"), NewFillColor)

Sub fillEm(rngToFill As Range, FillColor As Long)
Dim cell As Range
For Each cell In rngToFill
    With cell.Interior
      .Pattern = xlSolid
      .PatternColorIndex = FillColor
      .Color = FillColor
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
Next
End Sub

If you don't have all of the cells named, why don't you build your sub routine to handle one string at a time, then call it multiple times, rather than build the sub routine to handle multiple strings of cell addresses?

 

[JenniferMurphy]

Range("D3, G3, M3, D10") is a valid range so you wouldn't actually need to split that. The Range address argument is limited to 255 characters, so if you think you might exceed that I would suggest passing an array of strings and then looping through that since you seem to do the same formatting to every range.

I was about to test that, so thanks for confirming that option. I am unlikely to exceed 30 characters, so I should be good.

I would also note that there is no need to select the ranges to format them.

That's the code I got from the macro recorder. I'm not sure how to modify it to remove the selection. Would this work?

With Range(inRangeStr).Interior
  .Pattern = xlSolid
  .PatternColorIndex = xlAutomatic
  .color = colorFill
  .TintAndShade = 0
  .PatternTintAndShade = 0
End With

Thanks

 

[JenniferMurphy]

All of the cells are named, but they are named separately. I wasn't sure if I could create a range with a list of disjointed addressed. You and Rory have shows that I can.

I'd prefer to pass one list argument, rather than multiple calls, because then I only have to change the list variable if I add or remove cells.

Thanks

 

[RoryA]

Would this work?

With Range(inRangeStr).Interior

Yes it would.

 

[JenniferMurphy]

It's working great -- and much simpler.

Thanks Rory and Pat!