PDA

View Full Version : [SOLVED] Highlight minimum value ignoring 0



adraywin
08-18-2020, 12:35 AM
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
08-18-2020, 03:54 AM
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
08-18-2020, 04:29 AM
Conditional Formatting:

p45cal
08-18-2020, 06:38 AM
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
08-18-2020, 07:55 AM
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
08-18-2020, 08:04 AM
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
08-18-2020, 09:12 AM
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
08-19-2020, 02:47 AM
Thank you very much. It is working perfectly !