find entire string then row background red
 

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

find entire string then row background red
 

Hi Andrea,

Hope the below code helps!!!

Sub test()
Dim i As Integer
Dim mSearch As String
On Error GoTo HandleError
'This is your search string
mSearch = "test"
'Search for the string in column A
i = WorksheetFunction.Match(mSearch, Range("A:A"), 0)
'If found select the entire row
Range("" & i & ":" & i & "").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Range("A" & i).Select

HandleError:
End Sub

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


" 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

find entire string then row background red
 

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

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

find entire string then row background red
 

I the OP doesn't mind the overhead and only needs 3 colors plus the
background color, I would agree. If he will be changing colors or doesn't
want the overhead (or is using conditional formatting for some other purpose)
or needs more than 3, then perhaps not.

Nonetheless go ahead and lay it out.

--
Regards,
Tom Ogilvy



"JW" wrote:

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

find entire string then row background red
 

Hi Tom

I wondered if there is a way for your original macro to lookup the
exact values from sheet2 column A and then the colorindex value from
sheet2 column B. This would save me alot of time.

Please let me know if your macro can be revised to show this.

Thanks alot

Andrea

On Sep 19, 3:22 pm, 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- Hide quoted text -

- Show quoted text -