Find min 2 and max 2 cell values then highlight

I have searched, googled, and just can 't find the right code for this question, I hope someone can help me.

I want to identify the 2 minimum valued greater than 0.00% in rows with percentages only. Then identify the 2 maximum values greater than 0.00% in rows with percentages only. The sheet changes in size (rows and columns) each day. C4 Row 4 is a constant starting place if that is necessary. I have been tweaking this code all night, but no luck. I keep getting a highlighted yellow error in editor @ oRg.Find(What:=iMin, _
I also don't know what an oRg is, I am using 2001, that may be the problem.
Please help...

'   Sub FindMinValue()

    Dim oRg As Range, iMin As Variant
    Set oRg = Cells
    iMin = Application.Min(oRg)
    
    With oRg
    oRg.Find(What:=iMin, _
        After:=oRg.Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False _
        ).Select
    End With
    'Change selected cell format
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
    'Displaying min value info
    With Selection
        MsgBox "Min value : " & iMin & vbCrLf & _
        "Cell position " & vbCrLf & _
        "Row : " & .Row & vbCrLf & _
        "Column : " & .Column
    End With
 
    End Sub

I'm not actually sure your sample is correct, eg: Q10 should be pink as opposed to N10 as it's the 2nd smallest value

Highlight C3:Y50 and enter the following 2 conditions:

Top 2

=AND(MOD(ROWS(C$3:C3),2)=0,N(C3),SUM(IF(($C3:$Y3<>0)*($C3:$Y3<C3),1))<2)

Bottom 2

=AND(MOD(ROWS(C$3:C3),2)=0,N(C3),SUM(IF(($C3:$Y3<>0)*($C3:$Y3>C3),1))<2)

I'm not entirely convinced VBA is required here (given we've shown you can build excess capacity into the CF range), however, if you do need VBA post back and we can show how you can apply the above.

Related x-post can be found here

Should be able to record a macro and use relative cell references when recording it, just make sure you move down to the start of the next row/ rows, etc.

however...

Are you trying to use code to act as a custom conditional formatting alternative, or using a macro to set up conditional formatting across the sheet for you?

The only way I can come up with is (as always for me) with functions only applicable in 2007. I wrote some code that'll select any cell with % formatting (minus the one at the far right) and ignore 0's then conditionally format the remaining cells, based on how many there are, to either have the top and bottom 2 hightlight, or the top and bottom 1 highlight (if there are less than 4 cells which are not 0). This involves non-contiguous conditional formatting, which is not functional in 2003. So basically I'm out of ideas at the moment.

jpthelpguy,

if you want to do this with VBA you will need to use either a Worksheet_Change event or a Worksheet_Calculate event - which would be determined by how the values of interest are altered... if manually done then you can use the _Change event, if the values are the results of formulae updating then you must use a Calculate event.

I have shown you can do what you want using Conditional Formatting formulae (compatible across all versions) which can be applied across you entire range simultaneously from C3 to Yx where x can be row 100, 1000 whatever - ie build in some excess capacity... that said... those formulae are obviously set to Y... which as you say will not be constant... so to go back to your other post(s) - C3 is constant ... bottom right is not, correct ?