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

Thread: Search for a value then format a row

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

    Default

    Im looking to do the following task to help underline headings in a spreadsheet:

    Search for a cell containing the word "Phase"
    When it is found, underline the whole row
    ie If cell b6=Phase then
    underline all the cells in row 6

    Remember there may be quite a few cells containing the word Phrase

    thank you

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could try a macro that looks something like the following:

    Code:
    Sub FindAndUnderline()
    Dim rFound As Range, szFirst As String, iCount As Integer, myVl As String
    Dim myRow As Integer
    myVl = Application.InputBox(Prompt:="Please enter a search string", Title:="Howdy")
    Set rFound = Cells.Find(What:=myVl, LookAt:=xlWhole)
    iCount = 0
    Do While Not rFound Is Nothing
    If szFirst = "" Then
    szFirst = rFound.Address
    ElseIf rFound.Address = szFirst Then
    Exit Do
    End If
    myRow = rFound.Row
    With Range(myRow & ":" & myRow).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Set rFound = Cells.FindNext(rFound)
    Loop
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-10 09:19 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-10 08:50, dunk wrote:
    Im looking to do the following task to help underline headings in a spreadsheet:

    Search for a cell containing the word "Phase"
    When it is found, underline the whole row
    ie If cell b6=Phase then
    underline all the cells in row 6

    Remember there may be quite a few cells containing the word Phrase

    thank you
    You can use Conditional Formatting for this:

    Select the target cells, activate Format|Conditional Formatting, choose equal to from the box next to Value Is, and Phase in the white box. Then activate Format and choose from Underline on the Font tab. & finish.


  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This can be easily accomplished using Conditional Formatting. Do you care to pursue this approach? I'll need to know a bit more about your data. For example, does "Phase" only occur in a given column? Does case matter? BTW, by "underline" I assume you mean apply a border to the bottom of the entire row.

    [ This Message was edited by: Mark W. on 2002-04-10 09:26 ]

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Aladin, will this underline the whole row?

    Also, Dunk, take note, the following:

    Code:
    myVl = Application.InputBox(Prompt:="Please enter a search string", Title:="Howdy")
    Can easily be changed to:

    Code:
    myVl = "Phase"
    Depends if you want a prompt or not. You can also set it up with event triggers.

    Cheers,

    Nate

    [ This Message was edited by: NateO on 2002-04-10 09:29 ]

  6. #6
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    With Worksheets(1).Range("a1:a500")
    Set c = .Find("Phase", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    With c.EntireRow.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With

    Change your range to fit. This code was taken and adapted from the help file for the find function.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay dunk, while I await your response... and just to get a jump on my eager colleagues ...

    Assuming that you're concerned with occurances of "Phase" in column B. Select all rows of your data (using the Row heading buttons), choose the Format | Conditional Formatting... menu command, enter a "Formula Is" condition as =$B2="Phase", and apply a format with a (bottom) Border of your choosing.

    I think you find this much easier than VBA.

    [ This Message was edited by: Mark W. on 2002-04-10 09:39 ]

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

    Default

    Thanks for all your help
    I tried the first reply from natO
    and it works fine
    so i'll use this one.
    When I have time i'll go back and check out
    the other options.
    THANKS for all your help and options to solve my problem

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
  •