Search for a value then format a row

dunk

New Member
Joined
Feb 19, 2002
Messages
30
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-10 09:19
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top