Solved: Range Select from Found Cell

[Hyp3ri0n]

Hi All,

Hope this hasn't been answered already (couldn't find after a few searches) and is probably extremely easy..

I've established a find for a certain cell value "+/- PY", this could be anywhere on the sheet. From that cell, once it's found, I want to select the 6 columns to the right of that cell, including the column the cell is in and the next 200 Rows 2 rows below the found cell.
'
Cells.Find(What:="+/- PY", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Range("I13:N489").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="="""""
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(1).Borders(xlLeft).LineStyle = xlNone
Selection.FormatConditions(1).Borders(xlRight).LineStyle = xlNone
Selection.FormatConditions(1).Borders(xlTop).LineStyle = xlNone
Selection.FormatConditions(1).Borders(xlBottom).LineStyle = xlNone
Selection.FormatConditions(1).Interior.Pattern = xlNone
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0"
Selection.FormatConditions(2).Font.ColorIndex = 2
Selection.FormatConditions(2).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="0"
Selection.FormatConditions(3).Interior.ColorIndex = 4
Range("A5").Select

As you can see this is simply to grab the values under a heading of "+/- PY" and colour them accoring to their value, pity I can't wait for Office 07 on this one.

[mikerickson]

If you want A3:F203 when A1 is the found cell

Dim foundCell As Range, bigRange As Range
Set foundCell = Cells.Find(What:="+/- PY", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Set bigRange = foundCell.Range("A3:F203")

[Hyp3ri0n]

Absolutely spot on .. thanks Mike

[mikerickson]

You're welcome