find entire string then row background red
Depending on how many conditions you have, I believe Conditional
Formatting might be the way to go here.
Tom Ogilvy wrote:
Sub ColorCells()
Dim ans As String, ans1 As Long
Dim c As Range, rng2 As Range
Dim firstaddress As String
With ActiveSheet
Set rng2 = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
ans = InputBox("Enter string to search for")
ans1 = Application.InputBox("enter colorIndex number for color", Type:=1)
Set c = rng2.Find(What:=ans, _
After:=rng2(rng2.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.EntireRow.Interior.ColorIndex = ans1
Set c = rng2.FindNext(c)
Loop While c.Address < firstaddress
End If
End Sub
Worked for me.
--
Regards,
Tom Ogilvy
" wrote:
Hi Everyone
I dont know how easy this is but I wondered if this can be done in
code.
I need a macro to look down column A and search for an extract string
of text.
Everytime it finds a match it is then to highlight the row and format
the background of the row a specified colour.
I want to be able to use this macro for different string to colour all
my data.
I hope someone can help,
Many thanks
Andrea
|