Highlight Lowest Sales VBA

[billandrew]

Hi

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

Thanks

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Region[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]East[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]East[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]East[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]West[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]105[/TD]
[TD]West[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]106[/TD]
[TD]West[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]107[/TD]
[TD]North[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]108[/TD]
[TD]North[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]109[/TD]
[TD]North[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]South[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]South[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]South[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]Pacific[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD]Pacific[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD]Pacific[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.