Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro (Excel XP)

    I have 6 spreadsheets with all the same information (campus specific). What I would like to do is in a column that corresponds with the campus create "boxes" with borders. I'm not quite sure how to write the code. Can some savy VB expert help me?

    Thanks

    Racheal

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    I'm not sure exactly what you want to do. The code in <!post=This Post,421342>This Post<!/post> shows how to put a border around a cell.
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    (Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve spacing - see <!help=19>Help 19<!/help>)
    <pre>PHI 120
    Community X
    Desert Vista X
    Downtown X X X
    East X
    Northwest X
    West X X X
    PHI 122
    Community X
    Desert Vista X X X
    Downtown X
    East X
    Northwest X
    West X
    PHI 123
    Community X
    Desert Vista X X X
    Downtown X
    East X
    Northwest X
    West X X
    </pre>

    Above is part of the spread sheet layout

    Where it has the specific campuses I wanted to create "boxes" on that campus line but in another set of columns. I hope this clarifies better what I want

    Thanks for any and all of your help.

    Racheal

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Excel XP)

    It is not clear to me what you want. Could you elaborate some more?

    What do you mean by "boxes"?
    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    First, it would be much easier if you would upload a workbook with the data in it. That way we could see what actual columns and rows the data is actually in and we could use that to test the VBA code to do what you are asking.

    What you are asking is still not very clear. What is a "campus?" Is it PHI or 120 or Community. I would guess that it is Community which I would guess is in Column C, but could be column B or D or E or ... What other "set of columns" do you want boxes? The columns with the X's in them? If so, do you want boxes around all of the columns with X;s in them? What columns are the X's in? If not the columns with the X's, then what columns? Is it OK to make the "boxes" by putting borders around the cells, or are you looking for something else. Could you define what a box is?
    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    Attached is a sample of the worksheet and a comment explaining what I want to do.

    Racheal

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Excel XP)

    Still not entirely clear.
    Try a guess: Would cond formatting work?

    Select (eg) D4: G whatever
    Format - cells - Border(tab) -none
    Format - conditional formatting
    select from list: FormulaIs
    Enter the formula:
    =$C4<>""
    <format>
    Border(tab) - press "solid line" and "outline" button
    <ok><ok>

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    See if this does what you want:

    <pre>Public Sub InsertBorders()
    Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
    lLastRow = .Range("C65536").End(xlUp).Row - 1
    For I = 3 To lLastRow
    If .Range("C1").Offset(I, 0).Value <> "" Then
    With .Range("D1:G1").Offset(I, 0).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    Did it work for you?
    I tried to cut and past the code but it's not working

    Racheal

  10. #10
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    I probably followed your directions incorrectly as it did nothing.
    Racheal

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Excel XP)

    What do you mean by "nothing"

    It should pull up several dialog boxes at least
    Dialog1(Formatting)
    Initially it will"clear" the borders
    dialog2 (cond formatting)
    Then it will add borders around the cells that selected that have something in Cell C4

    What step fails?

    Steve

  12. #12
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    I selected the cell that I wanted with the borders and followed your steps. After I hit ok the cell still didn't have borders. If there has to be text in the cell then it will not work for what I want. Thanks
    Racheal

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Macro (Excel XP)

    There just has to be text in column C of that row to add the borders.

    Could you attach the demo sheet where you followed the directions?
    Steve

  14. #14
    New Lounger
    Join Date
    Nov 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    I will try it again but I will put a header on the column. If that doesn't work then I will forward you a copy.
    Thanks again
    Racheal

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel XP)

    I think I still did not understand what you wanted. I was putting borders around the cells in columns D:G. Looking at it again, I think you wanted the borders around the cells in I:L. Try the code below. If you want to put borders around both sets of columns, then uncomment the commented code.

    <pre>Public Sub InsertBorders()
    Dim I As Long, lLastRow As Long
    With Worksheets("Sheet1")
    lLastRow = .Range("C65536").End(xlUp).Row - 1
    For I = 3 To lLastRow
    If .Range("C1").Offset(I, 0).Value <> "" Then
    ' With .Range("D1:G1").Offset(I, 0).Borders
    ' .LineStyle = xlContinuous
    ' .Weight = xlThin
    ' .ColorIndex = xlAutomatic
    ' End With
    With .Range("I1:L1").Offset(I, 0).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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