Highlight Lowest Sales VBA

[billandrew]

Hi

Here I am looking to highlight (the entire row) the lowest sales by region in VBA.

Thanks

ID	Region	Sales
100	East	1000
102	East	1100
103	East	1200
104	West	1000
105	West	1100
106	West	1200
107	North	1000
108	North	1100
109	North	1200
110	South	1000
111	South	1100
112	South	1200
113	Pacific	100
114	Pacific	200
115	Pacific	300

<tbody>
</tbody>

 

[Scott T]

You can use conditional formatting instead of VBA.

Select your range assuming data starts in A1 then select A1:C16 and in conditional formatting use this formula.

=MIN($C$2:$C$16)=$C2

Note: If there are ties for min sales this will highlight all of them.

 

[Scott T]

If you must have VBA then this will highlight the row automatically when the value in column C is changed. This must go into the sheet module. Right click on the sheet tab and select view code and past the code there. The file must be saved as a macro enabled file type like .xlsm

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then
    Dim lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    Range("A2:C" & lr).Interior.Color = xlNone
    
    
    For x = 2 To lr
        If Cells(x, 3) = Application.WorksheetFunction.Min(Range("C2:C" & lr)) Then
            Range("A" & x & ":C" & x).Interior.ColorIndex = 6
        End If
    Next x


End If


End Sub

 

[billandrew]

It is necessary for this code to go into a private sub. I have other code which would precede this code.

 

[Scott T]

I do not think it has to, but that is what Excel defaults to when you create a worksheet change macro. It can also be modified to run when you run your other code if you like instead of a change event.

 

[My Aswer Is This]

I thought he wanted the lowest sales for each region.

So in this example there would be 4 rows highlighted.
One for each region.

And you can have more then one sheet change event script in your worksheet.

 

[Fluff]

I thought he wanted the lowest sales for each region.

Whereas I was thinking that the OP wanted to know which region had the lowest sales. In this case Pacific with 600 sales.

 

[billandrew]

This is correct. Looking to highlight lowest for each region

 

[Scott T]

Somehow missed that but this should work.

Sub hlmin()

Application.ScreenUpdating = False
Dim lr As Long
Dim ary() As Variant
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:C" & lr).Interior.ColorIndex = xlNone
mycount = lr - 1
ReDim ary(mycount, 1)

For x = 2 To lr
    For a = 0 To mycount
        ary(a, 0) = Cells(a + 2, 2)
        If Cells(x, 2) = Cells(a + 2, 2) Then
            ary(a, 1) = Cells(a + 2, 3)
        Else
            ary(a, 1) = "no"
        End If
    Next a
    mymin = WorksheetFunction.Min(ary)
    If Cells(x, 3) = mymin Then Range("A" & x & ":C" & x).Interior.ColorIndex = 6
    
Next x
Application.ScreenUpdating = True
End Sub

 

[billandrew]

Thought you forgot about me. lol. I will try it. Thank you.