Copying Conditional Formatting - Top n

[empower]

I have a spreadsheet with 3,000 rows and 105 columns. I have a conditional format rule to format the top 5 values on a row. I would like to have that on every row.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Unfortunately conditional formatting places un-removable $ in the values. Copying the condition just increases the size of the top 5 pool of values. Is there a way to do what I want without hand inserting the condition on each row?<o></o>
Thanks,<o></o>
Mark<o></o>

 

[empower]

Using version 2007

 

[empower]

Here is what i ended up doing, but it seems hacky. Any improvements would be welcomed.

Thanks,
Mark

Sub FormatRow()
    On Error Resume Next
    'Get current state of various Excel settings; put this at the beginning of your code
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
    'turn off some Excel functionality so your code runs faster
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
 
    Dim smValue
    Dim rng As Range
    Dim row As Range
    Dim cell As Range
    Set rng = Range("D5:DD3730")
    For Each row In rng.Rows
        smValue = WorksheetFunction.Small(row, 5)
        For Each cell In row.Cells
            On Error GoTo 0
            cell.FormatConditions.Delete
            cell.Select
            If cell.value <= smValue Then
                With cell
                    .Font.Bold = True
                    .Font.Italic = True
                    .Font.Color = vbRed
                End With
            End If
            'Do Something
        Next cell
    Next row
 
    'after your code runs, restore state; put this at the end of your code
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
    ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting
 
End Sub

 

[empower]

While im at it, what about adding icons on each row. The below works (Kinda) on the first row then bombs.

Sub FormatRow()
    On Error Resume Next
    'Get current state of various Excel settings; put this at the beginning of your code
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
    'turn off some Excel functionality so your code runs faster
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
    
    
   
    Dim smValue1, smValue2, smValue3, smValue4, smValue5
    Dim rng As Range
    Dim row As Range
    Dim cell As Range
    Set rng = Range("D5:DD3730")
    'Dim cfIconSet As IconSetCondition
    ' Create an icon set conditional format for the created sample data range.
      
    For Each row In rng.Rows
        smValue1 = WorksheetFunction.Small(row, 1)
        smValue2 = WorksheetFunction.Small(row, 2)
        smValue3 = WorksheetFunction.Small(row, 3)
        smValue4 = WorksheetFunction.Small(row, 4)
        smValue5 = WorksheetFunction.Small(row, 5)
      
        With row
            .Select
            .FormatConditions.Delete
            
            .FormatConditions.AddIconSetCondition
            .FormatConditions(1).IconSets (xl5Quarters)
            .FormatConditions(1).Type = XlConditionValueTypes.xlConditionValueFormula
            .FormatConditions(1).value = smValue1
            .FormatConditions(1).Operator = XlFormatConditionOperator.xlEqual
            
            .FormatConditions.AddIconSetCondition
            .FormatConditions(2).IconSets (xl5Quarters)
            .FormatConditions(2).Type = XlConditionValueTypes.xlConditionValueFormula
            .FormatConditions(2).value = smValue2
            .FormatConditions(2).Operator = XlFormatConditionOperator.xlEqual
            
            .FormatConditions.AddIconSetCondition
            .FormatConditions(3).IconSets (xl5Quarters)
            .FormatConditions(3).Type = XlConditionValueTypes.xlConditionValueFormula
            .FormatConditions(3).value = smValue3
            .FormatConditions(3).Operator = XlFormatConditionOperator.xlEqual
            
            .FormatConditions.AddIconSetCondition
            .FormatConditions(4).IconSets (xl5Quarters)
            .FormatConditions(4).Type = XlConditionValueTypes.xlConditionValueFormula
            .FormatConditions(4).value = smValue4
            .FormatConditions(4).Operator = XlFormatConditionOperator.xlEqual
            
            .FormatConditions.AddIconSetCondition
            .FormatConditions(5).IconSets (xl5Quarters)
            .FormatConditions(5).Type = XlConditionValueTypes.xlConditionValueFormula
            .FormatConditions(5).value = smValue5
            .FormatConditions(5).Operator = XlFormatConditionOperator.xlEqual
            
        End With
    
            For Each cell In row.Cells
                On Error GoTo 0
                cell.FormatConditions.Delete
                cell.Select
                If cell.value <= smValue5 Then
                    With cell
                        .Font.Bold = True
                        .Font.Italic = True
                        .Font.Color = vbRed
                    End With
                    
                End If
                'Do Something
            Next cell
        Next row
    
    
        'after your code runs, restore state; put this at the end of your code
        Application.ScreenUpdating = screenUpdateState
        Application.DisplayStatusBar = statusBarState
        Application.Calculation = calcState
        Application.EnableEvents = eventsState
        ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting
    
End Sub

 

[empower]

I can't get the Icon thing to work at all. I think i have it set up wrong. Any suggestions?