Consulting

Results 1 to 6 of 6

Thread: Solved: help

  1. #1

    Post Solved: help

    Dear friend,
    In the excel sheet, I want to border all row which are in dynamic range both in row and column side, autofit column width, keep filter in row no 1 and make that row in bold fant and color as white, background darker.

    I did everything. Can anybody rectify the error. It is unable to select dynamic range although I have mention
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]
    Sub Macro1()
    Dim rng As Range
    Dim lLastRow As Long, i As Long

    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A1:E1").Resize(lLastRow)

    rng.Borders(xlDiagonalDown).LineStyle = xlNone
    rng.Borders(xlDiagonalUp).LineStyle = xlNone
    Call SetBorder(rng, xlEdgeLeft)
    Call SetBorder(rng, xlEdgeTop)
    Call SetBorder(rng, xlEdgeBottom)
    Call SetBorder(rng, xlEdgeRight)
    Call SetBorder(rng, xlInsideVertical)
    Call SetBorder(rng, xlInsideHorizontal)

    Cells.Columns.AutoFit
    With Range("A1:E1")

    .AutoFilter

    With .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.349986266670736
    .PatternTintAndShade = 0
    End With

    .Font.Bold = True
    End With
    End Sub

    Private Function SetBorder(rng As Range, border As XlBordersIndex)
    With rng.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Thanks Xld.
    The macro work for column in dynamic range. Is it possible to keep the same for dynamic row also.
    Advance thanks

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Can you clarify what you mean by that?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    Dear Xld,
    Suppose few new columns are added. Then the macro can not bordered that output (It is limit upto column E, With Range("A1:E1")).
    Is it possible to border all column which are dynamically added like row

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]
    Sub Macro1()
    Dim rng As Range
    Dim lLastRow As Long
    Dim lLastCol As Long
    Dim i As Long

    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    lLastRow = Cells(1, Columns.Count).End(xlToLeft).Column
    Set rng = Range("A1").Resize(lLastRow, lLastCol)

    rng.Borders(xlDiagonalDown).LineStyle = xlNone
    rng.Borders(xlDiagonalUp).LineStyle = xlNone
    Call SetBorder(rng, xlEdgeLeft)
    Call SetBorder(rng, xlEdgeTop)
    Call SetBorder(rng, xlEdgeBottom)
    Call SetBorder(rng, xlEdgeRight)
    Call SetBorder(rng, xlInsideVertical)
    Call SetBorder(rng, xlInsideHorizontal)

    Cells.Columns.AutoFit
    With Range("A1").Resize(, lLastCol)

    .AutoFilter

    With .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.349986266670736
    .PatternTintAndShade = 0
    End With

    .Font.Bold = True
    End With
    End Sub

    Private Function SetBorder(rng As Range, border As XlBordersIndex)
    With rng.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
    End With
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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