highlight entire row when contains certain letters in word(s) in the whole worksheet

**purin0c0** · 09-06-2012, 02:50 PM

I want a vba that can highlight the entire row when certain letters were found in word(s) in cells
i found the below code, but it works when there is an exact match ONLY.

PHP Code:


Sub colorentirerow_ifvalue() 
  
 Dim r As Variant 
 Dim k As Variant 
 Dim i As Variant 
 
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select 
For Each r In Selection 
If r.Value Like "HUA" Then 
r.EntireRow.Interior.ColorIndex = 22 
End If 
Next 
 
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select 
For Each k In Selection 
If k.Value Like "KIND" Then 
k.EntireRow.Interior.ColorIndex = 21 
 
End If 
Next 
 
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select 
For Each i In Selection 
If i.Value Like "THANKS" Then 
i.EntireRow.Interior.ColorIndex = 20 
 
End If 
Next 
 
End Sub

i need
1. partial matches to be highlight too.
e.g. A cell contains "CHANGHUA" or "HUACHANG" to be found and highlight the entire row
2. treats uppercase or lowercase as the same value.
e.g. A cell contains "hua" or "Hua" to be found and highlight the entire row

THANKS!

**oeldere** · 09-06-2012, 03:08 PM

Maybe if you change this part of the code

Please Login or Register  to view this content.

In

Please Login or Register  to view this content.

**purin0c0** · 09-06-2012, 03:23 PM

Thanks oeldere!
it solved my first issue.
but it still can not find "hua" or "Hua"

**JOHN H. DAVIS** · 09-06-2012, 03:34 PM

This has not been tested, but a filter approach might work.

Please Login or Register  to view this content.

**tlafferty** · 09-06-2012, 03:46 PM

A couple of things:
1) selecting hinders performance
2) if you have repetitive blocks of code, it's a good idea to compress them

This isn't working, so perhaps it can be tweaked a bit...I'm sure the idea is dead on target:

Please Login or Register  to view this content.

**purin0c0** · 09-06-2012, 03:46 PM

Hi JOHN H. DAVIS, it doesnt work, it only highlight the first row

**JOHN H. DAVIS** · 09-06-2012, 03:50 PM

I just tested it and it worked for me. Which cell did you select as the activecell in your range?

**purin0c0** · 09-06-2012, 03:53 PM

to tlafferty

it doesnt work either.
nothing is highlighted

**purin0c0** · 09-06-2012, 03:57 PM

to JOHN H. DAVIS
i tried,it works only if the filtered words ("HUA" ,"KIND" and "THANKS") in Column A. In my case, it can be all over the entire worksheet.
Can it be fix?

**JOHN H. DAVIS** · 09-07-2012, 07:27 AM

How many columns do you have? This will handle 20, but you can increase the variable. There probably are a lot better ways.

Please Login or Register  to view this content.

**purin0c0** · 09-07-2012, 11:16 AM

i have 39 columns from column A to column AM

**JOHN H. DAVIS** · 09-07-2012, 11:18 AM

Then change this:

For i = 1 To 20

To this:

For i = 1 To 39

**purin0c0** · 09-07-2012, 11:28 AM

I tried with the code below

PHP Code:


Sub purin0c0()

Dim i As Long

For i = 1 To 39

Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=1, Criteria1:="=*HUA*"
Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.ColorIndex = 22
ActiveSheet.AutoFilterMode = False

Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=1, Criteria1:="=*KIND*"
Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.ColorIndex = 21
ActiveSheet.AutoFilterMode = False

Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).AutoFilter Field:=1, Criteria1:="=*THANKS*"
Range(Cells(1, i), ActiveCell.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.ColorIndex = 20
ActiveSheet.AutoFilterMode = False


Next i

End Sub

It did highlighted the lines i need.
but it also highlighted my first row and all blank rows after the active row to ColorIndex = 20

can it be fixed?

**JOHN H. DAVIS** · 09-07-2012, 12:34 PM

Maybe:

Please Login or Register  to view this content.

Again, probably not the best way.

**purin0c0** · 09-07-2012, 12:45 PM

that works great!!
THANKSS!!! JOHN!!!

**JOHN H. DAVIS** · 09-07-2012, 12:56 PM

You're welcome. Glad too help out and thanks for the feedback.

highlight entire row when contains certain letters in word(s) in the whole worksheet

LinkBack

Thread Tools

Rate This Thread

Display

highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Re: highlight entire row when contains certain letters in word(s) in the whole worksheet

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions