Highlighting an entire row based on a search criteria on tha

[CharmingUser]

I need to search for a keyword on multiple rows of datas and when that keyword is found, to highlight that row.

For example, I might be searching on the keyword "Total", and if that occurs on a row, that whole row is highlighted. Thanks for any help.

John

[Dave Hawley]

Hi

Try Conditional formattng found under "Format". You select your rows startinf from row 1 then use the "Formula is" Option with a formula like:

=COUNTIF(1:1,"Total")>0

The pick your format for the row.

[NateO]

Conditional formatting would be automatic, but you could also use vba, like the following:

Sub RwColor()
Dim rFound As Range
Dim sFirst As String
Dim iCount As Integer
Dim myval As Variant

myval = Application.InputBox("Enter Search String")
Set rFound = Cells.Find(What:=myval, LookAt:=xlPart, LookIn:=xlValues)
iCount = 0
Do While Not rFound Is Nothing
If sFirst = "" Then
sFirst = rFound.Address
ElseIf rFound.Address = sFirst Then
Exit Do
End If
rFound.EntireRow.Interior.ColorIndex = 32
iCount = iCount + 1
Set rFound = Cells.FindNext(rFound)
Loop
End Sub


Cheers, Nate

[ This Message was edited by: NateO on 2002-03-11 09:23 ]

[CharmingUser]

Thanks for the tips guys, the VBA code is exactly what I need. Just to throw a wrinkle in, instead of highlighting the entire row, can I make it check, so it only highlight up to the point where it has data? This way, I don't have to cut the parts with the data only and paste it onto another sheet. It's purely for aesthetics. Thanks for your help again.

John

[NateO]

Hope I'm following your question....Highlight column A to the data in question? Perhaps the following will work:

Sub RwColor2()
Dim rFound As Range
Dim sFirst As String
Dim iCount As Integer
Dim myval As Variant

myrange = ActiveCell.Address
myval = Application.InputBox("Enter Search String")
Set rFound = Cells.Find(What:=myval, LookAt:=xlPart, LookIn:=xlValues)
iCount = 0
Do While Not rFound Is Nothing
If sFirst = "" Then
sFirst = rFound.Address
ElseIf rFound.Address = sFirst Then
Exit Do
End If
rw = rFound.Row
Range("a" & rw & ":" & rFound.Address).Interior.ColorIndex = 32
iCount = iCount + 1
Set rFound = Cells.FindNext(rFound)
Loop
Range(myrange).Select
End Sub

Hope that helps. Cheers,

Nate

[ This Message was edited by: nateo on 2002-03-11 13:30 ]