+ Reply to Thread
Results 1 to 4 of 4

How do you pass a column range as an argument to a subroutine?

Hybrid View

  1. #1
    Registered User
    Join Date
    02-17-2008
    Posts
    21

    How do you pass a column range as an argument to a subroutine?

    Perhaps my question is too simple as I cannot find an appropriate answer in the forums or online references.

    I need to interactively format many iterated worksheets that are generated with an external program. Once done, I need to add new columns and format those columns. There is much repetition in the columns (comparisons against an index sheet) and repeating all that formatting code is unwieldy and I suspect slowing down performance. So to get to it:

    Sub MainOne()
    Dim ColumnVar as Range
    
    ColumnVar = "C:C"
    
    FormatItNow(columnVar)
    
    <<rest of code>>
    
    End Sub
    
    
    Sub FormatItNow(ColumnVar)
    Range(ColumnVar).Select
    
    With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 13551615
            .TintAndShade = 0
    End With
    etc
    
    End Sub
    This pseudocode example would give me an error (type mismatch) every time. Futzing with quotes did no good.

    Any help on the correct syntax would be very much appreciated.

    Thank you in advance.
    Last edited by parodytx; 06-27-2013 at 03:21 PM. Reason: forgot code formatting

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How do you pass a column range as an argument to a subroutine?

    Try:

    Sub MainOne()
    Dim ColumnVar As Range
    
    Set ColumnVar = Range("C:C")
    
    Call FormatItNow(ColumnVar)
    
    '<<rest of code>>
    
    End Sub
    
    
    Sub FormatItNow(ColumnVar As Range)
    
    With ColumnVar.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
    End With
    'etc
    
    End Sub
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    02-17-2008
    Posts
    21

    Re: How do you pass a column range as an argument to a subroutine?

    OK that makes sense. I'll try it tonight.

    Thanks again.

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: How do you pass a column range as an argument to a subroutine?

    Something like
    Sub MainOne()
    Dim ColumnVar As Range
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ColumnVar = ws1.Range("C:C")
    
    FormatItNow ColumnVar
    
    End Sub
    
    
    Sub FormatItNow(ColumnVar As Range)
    Dim newCvar As Range
    
    'Set newCvar = ColumnVar
    With ColumnVar.Cells.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    
    
    End Sub
    EDIT: Sorry xl, you beat me to it.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1