Min Value in column

[billandrew]

Happy New Year all

Today I have been attempting to apply the following formula (Highlight Min Value) to a range of columns (A:F) using VBA any suggestions.

=(A1<>0)*(A1<=SMALL(A$1:A$50,COUNTIF(A$1:A$50,0)+1))

Could use small without looking for values <>0

Thank You

 

[mrhstn]

Hi

Are you looking for the min value in the column or the nth smallest value based on how many 0's are in the column?

 

[billandrew]

Min value in column

 

[mrhstn]

You could use this in CF

=A1=MIN(A$1:A$50)

You say you want to do this with VBA?

 

[billandrew]

Yes sir

 

[mrhstn]

You can try this

Sub FindMn()
Dim cCol As Integer
Dim rRow As Integer
For cCol = 1 To 6 ' Columns A to F
    For rRow = 1 To 50 ' Rows 1 to 50
        If Cells(rRow, cCol) = WorksheetFunction.Small(Columns(cCol), 1) Then
            Cells(rRow, cCol).Interior.Color = rgbAqua
        End If
    Next rRow
Next cCol
End Sub

 

[billandrew]

Works perfect.

Thank You. Happy New Year...

 

[mrhstn]

You're welcome, thanks for the feedback. Happy new year...