Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Formatting in VBA

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In an excel spreadsheet I'm looking to find a way to search the entire spreadsheet, find the "Dept Total" cells, go to the cell to the right of it(this contains a currency value)and put a top border line above this cells contents. This will make a visible split between lots of Dept Totals.

    Any help would be very welcome

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dunk

    here is one way to do this;

    Sub finder()
    Dim SearchString As String
    Dim Sh, F, Location As String, Flag, Found1st

    SearchString = "Dept Total"
    Set Sh = Sheets.Application

    For Each Sh In Application.Sheets
    Flag = 0
    With Sh.Range("A1:IV65536")
    Do
    Set F = .Find(SearchString, lookat:=xlWhole, after:=ActiveCell, LookIn:=xlValues)
    If Not F Is Nothing Then
    If Flag = 0 Then Found1st = F.Offset(0, 1).Address(external:=True)
    Location = F.Address
    Sh.Select
    Range(Location).Offset(0, 1).Select
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    Flag = Flag + 1
    End If
    Loop Until Found1st = ActiveCell.Address(external:=True) And Flag > 2 Or F Is Nothing
    End With
    Next Sh

    End Sub


    HTH

    Ivan

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works great!!
    thanks very much

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm now looking to modify the above code so that it formats the next nine cells in the same row, and not just the next cell.

    Any help appreciated

    dunk

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Any help would be greatly appreciated.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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