Search for a value then format a row

[dunk]

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

[NateO]

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

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 ]

[Aladin Akyurek]

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.

[Mark W.]

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 ]

[NateO]

Hey Aladin, will this underline the whole row?

Also, Dunk, take note, the following:

myVl = Application.InputBox(Prompt:="Please enter a search string", Title:="Howdy")

Can easily be changed to:

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 ]

[Al Chara]

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.

[Mark W.]

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 ]

[dunk]

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