Using conditional formatting in 2007

[craigwg]

Have a block of cells I need to analyze row by row with conditional formatting. The problem is I have to do it row by row. Eventuall I will have thousands of rows, so that's really not an option. If I select the entire range at once and tell it to color the bottom 2 values red, then it analyzes the entire block at once. I need it to do it row by row, so each row has the lowest two values formatted.

I hope that makes sense.

Is there a way to do this?

Thanks

[craigwg]

Hey hey, I got it!

This worked just fine for me:

Sub Macro1()
'
' Macro1 Macro
'
'

Do
'Range("E13:O13").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1)
.TopBottom = xlTop10Bottom
.Rank = 2
End With
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 10)).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

[Aussiebear]

When posting code to the forums, please select the code portion and then click the green & white VBA button. This allows the code to be formatted correctly and therefore much easier to read.