VBA code to highlight a specific text in cell

[vane0326]

Is there a VBA code if in collumn (A) has the word "Dog" then say it found it in (A16) then hilghlight the rows thru (A16 to G16). Conditional formatting wont work for what i'm asking for but I wont bore you for my reasons why, But I'll keep this short. Is there a VBA code for that?

Thanks!

 

[Tom Urtis]

Sub Test2()
With Application
.ScreenUpdating = False
Cells.ClearFormats
Dim Valz, i, cell As Range, RangeToFormat As Range
Valz = Array("Dog", "Cat")
For i = LBound(Valz) To UBound(Valz)
For Each cell In Columns(1).SpecialCells(2)
If cell.Value = Valz(i) Then
If RangeToFormat Is Nothing Then
Set RangeToFormat = Range(cell, cell.Offset(0, 6))
Else
Set RangeToFormat = .Union(RangeToFormat, Range(cell, cell.Offset(0, 6)))
End If: End If: Next cell: Next i
On Error Resume Next
RangeToFormat.Select
Err.Clear
.ScreenUpdating = True
End With
End Sub

 

[vane0326]

its dog thats the only text and cat is the only text.
How about PA HS Teacher Idea?

Also Tom Urtis code just highlight cat also.
Any Ideas?

Book2
	A	B	C	D
1
2
3
4	Dog
5
6
7
8
9
10
11
12
13
14
15
16
17
18	Cat
19
20
Sheet1

 

[Tom Urtis]

No, my code hilited both Dog and Cat, it works fine, I tested it several times. Try it again, making sure you really have those values in the cells, and not something like " Cat" or "Dog " (notice the space).

 

[vane0326]

Your Right Now it works Thanks for the code.
also Tom Urtis I apologize it I did not make myself clear in the early posts.

Thanks for your help guys! :wink:

 

[Tom Urtis]

That's fine, and if you need those rows to be selected based on criteria (Dog, Cat, whatever) as part of a larger string value in column A, this would have done that:

Sub Test3()
With Application
.ScreenUpdating = False
Cells.ClearFormats
Dim Valz, i, cell As Range, RangeToFormat As Range
Valz = Array("Dog", "Cat")
For i = LBound(Valz) To UBound(Valz)
For Each cell In Columns(1).SpecialCells(2)
If InStr(cell.Value, Valz(i)) > 0 Then
If RangeToFormat Is Nothing Then
Set RangeToFormat = Range(cell, cell.Offset(0, 6))
Else
Set RangeToFormat = .Union(RangeToFormat, Range(cell, cell.Offset(0, 6)))
End If: End If: Next cell: Next i
On Error Resume Next
RangeToFormat.Select
Err.Clear
.ScreenUpdating = True
End With
End Sub