Hyp3ri0n
10-09-2007, 07:11 AM
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.
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.