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!

 

[Legacy 98055]

I'm done.

 

[Tom Urtis]

I don't know where else to go with this, I don't understand the follow-up question because the values in H1 and H2 are how the criteria are determined by the user, which means the macro is flexible to do what you say you want. Change column H as the criteria-containing column to some other convenient column if you need, and modify the code accordingly.

 

[vane0326]

I'm sorry guys I guess I'm not explaing correctly. When you go to in excel click the whole collumn in (A) then hit Edit then hit go to: you have those options to pick. say: Texts, numbers, formulas, or error and when you choose numbers it will only highlight the numbers in collumn (A) then from there you could do anything choosing what type of borders or changing the font size colors or just click clear cells. Now if we can use that go to special comand function using vba to search for a specfic text and when it highlight that text it highlight that row to the criteria I'm asking for. then I will record a simple macro then I start formating like changing the font size and put whatever border I pick, then I will take that macro that I just recorded and add it to the code. So next time that word pops up in collumn (A) I will run that macro and will it format that text including the row.

Hopefully you understand what I'm saying.

Any Ideas?

 

[chiello]

Does this work??

Code:
Sub Macro2()
Dim iCELL As Range, DataRange As Range

Set DataRange = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
For Each iCELL In DataRange
If InStr(1, LCase(iCELL), "dog") > 0 Or InStr(1, LCase(iCELL), "cat") > 0 Then
Range(iCELL, iCELL.Offset(0, 6)).Select
'Insert the code to format
Else
'Range(iCELL, iCELL.Offset(0, 6)).Interior.ColorIndex = xlNone
End If
Next iCELL
End Sub

 

[vane0326]

Thank You it works perfect!!!

 

[vane0326]

But it only highlight cat not dog can it highlight both of them?

Thanks!

 

[chiello]

If InStr(1, LCase(iCELL), "dog") > 0 Or InStr(1, LCase(iCELL), "cat") > 0 Then

Actually, it should highlight both cat and dog!!

 

[vane0326]

I'm keep on trying and it only still highlight cat also I replace "cat" in the code and change it to "Without Pattern Changes" and does not highlight. But it highlight "dog". Why is that ?
Sub Macro2()
Dim iCELL As Range, DataRange As Range

Set DataRange = Intersect(Columns("A:A"), ActiveSheet.UsedRange)
For Each iCELL In DataRange
If InStr(1, LCase(iCELL), "dog") > 0 Or InStr(1, LCase(iCELL), "Without Pattern Changes") > 0 Then
Range(iCELL, iCELL.Offset(0, 6)).Select
'Insert the code to format
Else
'Range(iCELL, iCELL.Offset(0, 6)).Interior.ColorIndex = xlNone
End If
Next iCELL
End Sub


Any Ideas?

 

[PA HS Teacher]

I don't know why chiello's code would highlight just one. I thought his code was pretty slick, and I can't think of why it would only highlight "cat" cells.

What happens if you comment out the Or part of his statement, like this:

If InStr(1, LCase(iCELL), "dog") > 0  'Or InStr(1, LCase(iCELL), "cat") > 0 Then

Also, (And I have no idea why this would make a difference)

but what if instead of using the Or Statement, you used two If statements.

If Cat then ....

If Dog then ...

 

[chiello]

Sorry, I don't understand why it doesn't work, It's over my knowledge!!

Can you please provide the whole text of a cell containing "dog" which is not highlighted??