find and highlight

[mariasa]

Hi Guys,

could u please help me with the following macro:

i need it to go thru the column J of each of my worksheets (worksheet
names are 2006, 2005, 2004, 2003, 2002, 2001, 2000, 1999) and if it
finds "GR" as part of the name in the cell (say IFX GR - thats a
security id in bloomberg), it has to highlight the entire row ( i have
numerous columns in the sheet) and if it doesnt find "GR" it should do
nothing of course. I need this because i need to isolate (or highlight)
the german secondary offerings from the list of european ones.

Thanks so much in advance for your help!
mariasa

 

[Guest]

Try this:


Sub Find_GR()

Dim lastrow As Long
Dim wsname As String
Dim ws As Integer

For ws = 1999 To 2006
wsname = Trim(Str(ws))
Worksheets(wsname).Activate
With Worksheets(wsname)
lastrow = .Cells(Rows.Count, "J").End(xlUp).Row
With .Range("J1:J" & lastrow)
Set c = .Find("GR", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row).Interior.ColorIndex = 3
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End With
Next ws
End Sub


HTH

 

[Guest]

Another method - doesn't care what your sheet names are, examines all in the
workbook (which may not be what you want)

Sub HighlightGREntries()

Dim AnySheet As Worksheet
Dim ThisSheetName As String
Dim CurrentSelection As String

ThisSheetName = ActiveSheet.Name
CurrentSelection = ActiveCell.Address

Application.ScreenUpdating = False
For Each AnySheet In ThisWorkbook.Worksheets
Worksheets(AnySheet.Name).Activate
Range("G65535").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
Do While ActiveCell.Row > 1
ActiveCell.Offset(-1, 0).Activate
If InStr(ActiveCell, "GR") Then
With Selection.EntireRow
.Interior.ColorIndex = 34 ' teal
'other color codes
'Red = 3
'Blue = 5
'pale yellow = 36
'bright yellow = 6
End With
Else
With Selection.EntireRow
.Interior.ColorIndex = xlNone
End With
End If
Loop
Next ' AnySheet Worksheet loop
'back to where we started
Worksheets(ThisSheetName).Activate
Range(CurrentSelection).Select
Application.ScreenUpdating = True
End Sub

 

[mariasa]

Thank you for ur response, Toppers. I tried it out and for some reason
it performed the sub only on ws 2006 and the rows it highlighted were
not exclusively the ones that had GR in column J as part of the string.
For example IPN FP Equity was the one picked up when looking for GR.
Strange..Do you have an idea of what could have gone wrong?

Thanks

Maria

 

[mariasa]

JLatham, thanks a lot for ur suggestion but however when i tried it,
nothing changed in the workbook. No idea why that would be the
case...Any further advice?

Thanks,
Maria

 

[Guest]

Maria

I tried it with IFN FP Equity in Column J and it was NOT selected; I see no
possible reason for it selectin "IFN FP Equity". When I changed it to "IFN FP
Equity GR" then it was changed!

In my workbook I had worksheets 1999 and 2000 as a test and BOTH were changed.

So I don't have a clue as to why yours is not working.

Did you simply cut & paste the code?

 

[Guest]

If you want, post w/b to toppers<at>johntopley.fsnet.co.uk and I will have a
look at it.

 

[mariasa]

Toppers,

my bad, i think the issue was in the ordering of the worksheets. it wa
2006 to 1999 and i did it 1999 to 2006 this time and it worke
perfectly, so yea, thanks a lot for ur code, i seem to be all set no


Kind Regards,
Mari