Results 1 to 9 of 9

change format of part of column

Threaded View

  1. #1
    Registered User
    Join Date
    10-26-2015
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    36

    Talking change format of part of column

    Hi, I'm trying to make my code change the format of 9 columns. they all have the same amount of rows but these may differ each time the macro runs. I tried the following code with an array, but it doesnt work at all. Can you please help me to adjust it 'till it works? and maybe tell me what I did wrong?

    Thanks in advance.
    Function Format_all()
    Dim wsO as worksheet
    Dim f As Long
    Dim format() As String
    
    For f = 1 To 9
    Set format(1) = wsO.Range("A16:A" & wsO.Range("A16").End(xlDown).Row)
    Set format(2) = wsO.Range("B16:B" & wsO.Range("B16").End(xlDown).Row)
    Set format(3) = wsO.Range("C16:C" & wsO.Range("C16").End(xlDown).Row)
    Set format(4) = wsO.Range("D16:D" & wsO.Range("D16").End(xlDown).Row)
    Set format(5) = wsO.Range("E16:E" & wsO.Range("E16").End(xlDown).Row)
    Set format(6) = wsO.Range("F16:F" & wsO.Range("F16").End(xlDown).Row)
    Set format(7) = wsO.Range("G16:G" & wsO.Range("G16").End(xlDown).Row)
    Set format(8) = wsO.Range("H16:H" & wsO.Range("H16").End(xlDown).Row)
    Set format(9) = wsO.Range("I16:I" & wsO.Range("I16").End(xlDown).Row)
    
    With format(f).Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With format(f).Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With format(f).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With format(f).Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        Next f
    end function
    Also, this is all the code I use for this function so nothing else in "option explicit"
    Last edited by stielo; 12-01-2015 at 11:03 AM. Reason: solved

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro to change 1) part of string, 2) the order of characters inside a string, 3) format
    By Karl Gustaf Karsten in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-16-2015, 11:51 AM
  2. [SOLVED] Conditional format part of a column based on a value
    By rickycr in forum Excel General
    Replies: 5
    Last Post: 10-16-2015, 10:54 AM
  3. want to change this part of the code to look between column numbers rather then range
    By justlearning123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2015, 03:31 PM
  4. [SOLVED] Change color of row if part is detected in column A
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2015, 12:31 PM
  5. Filter function doesn't change with column format change
    By sabrefan1970 in forum Excel General
    Replies: 0
    Last Post: 02-21-2014, 11:55 AM
  6. [SOLVED] Change Format of Column to Phone Format
    By basketball2524 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2013, 10:38 AM
  7. Get Autofit in Excel to format just part of a column?
    By Dlegrand in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-19-2006, 04:50 AM

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