Highlight minimum value ignoring 0

[adraywin]

Hi all,

I have an excel to compare the prices given by suppliers. Each column is to compare the prices submitted by the suppliers for the item that they were asked to quote which consists of item price, mold price, total price with mold=item price+(mold price/quantity).


I would like to:
highlight the minimum value for total price with mold, that is higher than 0 and also ignoring blank.
loop through the each column until there is no more items in row 2.

I managed to find the following code while googling that partially fit my need, but the problem is that it keeps highlighting 0 or blank value.

I have attached the intended result that I should see.

Option Explicit

Sub lowest()

    Dim lowestAs Double ' Long for whole numbers.
    Dim rng As Range
    Dim cell As Range

    With Worksheets("Sheet1")
        Set rng = .Range("b5,b8,b11,b14,b17,b20")
        lowest= Application.WorksheetFunction.Min(rng)
        Debug.Print worstcase

        For Each cell In rng
            If cell.Value = lowest Then
                cell.Interior.Color = rgbYellow ' Hightlight only cell.
            End If
        Next

    End With

End Sub

Thank you

[p45cal]

try this on the file you attached:

Sub blah()
OfsetAddr = "A4,A7,A10,A13,A16,A19"
With Sheets("Sheet1")
  For Each cll In .Range("B2", .Range("B2").End(xlToRight)).Cells    'headers
    Set rng = cll.Range(OfsetAddr)
    'rng.Select
    cll.Offset(1).Resize(18).Interior.Color = xlNone    'clears fill from column
    lowest = 1E+99    'a big number
    'loop 1 to find lowest with conditions:
    For Each celle In rng.Cells
      'celle.Select
      If Not (celle.Value = 0 Or Len(celle.Value) = 0) Then lowest = Application.Min(celle.Value, lowest)
    Next celle
    'loop 2 to highlight lowest:
    For Each celle In rng.Cells
      If celle.Value = lowest Then celle.Offset(-2).Resize(3).Interior.Color = rgbYellow    'highlights cell and the 2 cells above.
    Next celle
  Next cll
End With
End Sub

[snb]

Conditional Formatting:

[p45cal]

A tweak to snb's conditional format to:

=OR(B3=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)),B2=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)),B1=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)))

will handle zeroes.

[adraywin]

try this on the file you attached:

Sub blah()
OfsetAddr = "A4,A7,A10,A13,A16,A19"
With Sheets("Sheet1")
  For Each cll In .Range("B2", .Range("B2").End(xlToRight)).Cells    'headers
    Set rng = cll.Range(OfsetAddr)
    'rng.Select
    cll.Offset(1).Resize(18).Interior.Color = xlNone    'clears fill from column
    lowest = 1E+99    'a big number
    'loop 1 to find lowest with conditions:
    For Each celle In rng.Cells
      'celle.Select
      If Not (celle.Value = 0 Or Len(celle.Value) = 0) Then lowest = Application.Min(celle.Value, lowest)
    Next celle
    'loop 2 to highlight lowest:
    For Each celle In rng.Cells
      If celle.Value = lowest Then celle.Offset(-2).Resize(3).Interior.Color = rgbYellow    'highlights cell and the 2 cells above.
    Next celle
  Next cll
End With
End Sub

Thank you very much for your help.
It works wonderful in the test file that I have attached in my original post, I just had to declare the variables. But I don't know why it is not working in my real file even after making the necessary change.

I am attaching another file which is way closer to my real file in terms of structure and formatting.

The relevant value that should be compared is still the same: Total W/ amm CNY in R12,R19,R26,R33,R40,R47,R54,R61,R68,R75,R82,R89, and loop from column C4 to the right until it's empty.

Following rows should be ignored from the VBA code when highlighting the lowest value:
R91 to R94 is just the summary of which item I want to give to which supplier The reason it is here is because sometimes even if a supplier is the cheapest for an item they won't win because of non-cost related factors.
R95 to R106 is just for some benchmarking of prices and not relevant.

[adraywin]

A tweak to snb's conditional format to:

=OR(B3=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)),B2=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)),B1=MIN(IF(B$3:B$20>0,B$3:B$20,9E+99)))

will handle zeroes.

thank you for your help snb and p45cal with the conditional formatting solution !

I have actually thought of using conditional formatting which is very convenient.
But I often give items to suppliers even if they are not the cheapest, so it means that I would have to manually go to manage rule everytime and cancel the conditional rule If I want to highlight something else. Right ? Since when I tried, I can't just simply fill in color with nothing on top of a conditional formatting when it doesn't suits me
The reason I wanted to have it in VBA is because it is way easier to just remove a highlight (I have put a shortcut in my quickbar) and then highlight another item manually.

I hope my explanation makes sense. Thanks again both of you for sharing your knowledge.

[p45cal]

Sub blah()
Dim OfsetAddr, cll As Variant, rng As Range, lowest As Double, celle

OfsetAddr = "A9,A16,A23,A30,A37,A44,A51,A58,A65,A72,A79,A86"
With Sheets("Sheet1")
    For Each cll In .Range("C4", .Range("C4").End(xlToRight)).Cells    'headers
    Set rng = cll.Range(OfsetAddr)
    cll.Offset(2).Resize(84).Interior.Color = xlNone    'clears fill from column
    lowest = 1E+99    'a big number
    'loop 1 to find lowest with conditions:
    For Each celle In rng.Cells
      If Not (celle.Value = 0 Or Len(celle.Value) = 0) Then lowest = Application.Min(celle.Value, lowest)
    Next celle
    'loop 2 to highlight lowest:
    For Each celle In rng.Cells
      If celle.Value = lowest Then celle.Offset(-6).Resize(7).Interior.Color = rgbYellow    'highlights cell and the 6 cells above.
    Next celle
  Next cll
End With
End Sub

[adraywin]

Thank you very much. It is working perfectly !