matty101



I have a long section of data that needs to be broken up into 4/5 columns - which I've managed to do. Before I run the macro I have, the only data is in column A, and the macro fills in colums B to F. What I need help with, is highlighting a row (from column A to F) whenever the letters MASC appear in column C, after the first bit of my macro is run. I did try to adjust the codes I found on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1034778&SiteID=1 but with no luck at all. If that don't make sense, let me know.

Any help on this would be much appreciated

Thanks
Matty




Re: Highlighting a row when letters appear in cell?

Andy Pope


This is a variation of the Hilite routine. It uses find rather than testing each row at a time.

Sub Hilite()

Dim lngRow As Long
Dim rngFind As Range
Dim strFirstAddress As String

Application.ScreenUpdating = False

With Range("C:C")
' find cells in column C which contain only MASC
Set rngFind = .Find("MASC", LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
' remember where we started in order to end looping
strFirstAddress = rngFind.Address
Do
' go left 2 columns and then set pattern of next 6 to RED
rngFind.Offset(0, -2).Resize(1, 6).Interior.ColorIndex = 3
' find next occurance
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With
Application.ScreenUpdating = True

End Sub







Re: Highlighting a row when letters appear in cell?

matty101

Andy

Thatí»s great, exactly what I was looking for, works perfect.

If I also wanted it highlight other 4 letter words in column C (for instance SUGA SUGB CSWA), what would be the best way to do that I tried dupilcating your code, but with the above leters, and an error appears saying I've duplicated in the current scope






Re: Highlighting a row when letters appear in cell?

Andy Pope

You could modify the routine like this.

Sub Hilite()

Dim lngRow As Long
Dim rngFind As Range
Dim strFirstAddress As String
Dim vntToFind As Variant

Application.ScreenUpdating = False

With Range("C:C")
For Each vntToFind In Array("MASC", "SUGA", "SUGB","CSWA")
' find cells in column C which contain text stored in vntToFind variable
Set rngFind = .Find(vntToFind, LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
' remember where we started in order to end looping
strFirstAddress = rngFind.Address
Do
' go left 2 columns and then set pattern of next 6 to RED
rngFind.Offset(0, -2).Resize(1, 6).Interior.ColorIndex = 3
' find next occurance
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
Set rngFind = Nothing
Next
End With
Application.ScreenUpdating = True

End Sub

I assume you got your error because you also copied the Dim lines.







Re: Highlighting a row when letters appear in cell?

matty101

Andy

That code works great! You, sir, are genuis and I salute you!!.

Thanks for all your help mate - really appreciated

By the way, yes, I did copy the Dim lines