find and highlight

mariasa · #1 07-01-2006, 12:16 AM

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

#2 07-01-2006, 01:10 AM

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
"mariasa" wrote:

>
> 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
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=557535
>
>

#3 07-01-2006, 01:15 AM

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" wrote:

>
> 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
>
>
> --
> mariasa
> ------------------------------------------------------------------------
> mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=557535
>
>

mariasa · #4 07-01-2006, 01:17 AM

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 · #5 07-01-2006, 01:23 AM

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

#6 07-01-2006, 02:50 AM

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?

"mariasa" wrote:

>
> 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
> ------------------------------------------------------------------------
> mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> View this thread: http://www.excelforum.com/showthread...hreadid=557535
>
>

#7 07-01-2006, 02:55 AM

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

"Toppers" wrote:

> 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?
>
>
>
> "mariasa" wrote:
>
> >
> > 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
> > ------------------------------------------------------------------------
> > mariasa's Profile: http://www.excelforum.com/member.php...o&userid=31726
> > View this thread: http://www.excelforum.com/showthread...hreadid=557535
> >
> >

mariasa · #8 07-01-2006, 02:57 AM

Toppers,

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

Kind Regards,
Maria

Welcome to the Excel Forum

Forum Statistics