Highlight Lowest Value in a column

[shan]

Hello Everybody,

Request your assistance for a macro which will highlight lowest value row in yellow color corresponding the the another column.

As given in below screen shot code should check the Column A i.e. Sector and highlight the Lowest amount which is available in column D

Here for BOMDEL 10 and for MAABLR 1 is the lowest value




Sector
Origin
Destination
Amount


BOMDEL
BOM
DEL
12


BOMDEL
BOM
DEL
10


BOMDEL
BOM
DEL
29


BOMDEL
BOM
DEL
13


BOMDEL
BOM
DEL
14


BOMDEL
BOM
DEL
16


BOMDEL
BOM
DEL
72


MAABLR
MAA
BLR
2


MAABLR
MAA
BLR
10


MAABLR
MAA
BLR
34


MAABLR
MAA
BLR
52


MAABLR
MAA
BLR
87


MAABLR
MAA
BLR
9


MAABLR
MAA
BLR
1




Thanks
Shan

[Bob Phillips]

I would suggest no VBA, just use conditional formatting with a formula of

=MIN(IF($A$2:$A$15=A2,$D$2:$D$15))=D2

[shan]

Thank for your Sir.

The formula is not giving me a required output.

Also, I need the ranges to be dynamic...

[mancubus]

i would go with xld's suggestion. ranges can be made dynamic in formulas.



Sub vbax_54349_MinIf_in_VBA()

Dim rng1 As Range, rng2 As Range
Dim LastRow As Long

With Worksheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rng1 = .Range("A2:A" & LastRow)
Set rng2 = .Range("D2:D" & LastRow)

For i = 2 To LastRow
If .Range("D" & i).Value = Evaluate("=MIN(IF(" & rng1.Address & "=" & .Range("A" & i).Address & "," & rng2.Address & "))") Then
.Range("A" & i & ":D" & i).Interior.Color = vbYellow
End If
Next i
End With

End Sub

[shan]

Thank you Sir for your reply..
But I am not getting the desired output.

May be I am doing something wrong...

[mancubus]

perhaps.
because i'm getting the desired output. i just copied/pasted the table in the first message in a blank worksheet.

[shan]

Yes Sir .. You are right.

I was wrong ...

Sir one more help can we make some changes in code by which it will take less time when ran on huge data range!!

[mancubus]

since Evaluate function is used i am not sure this will improve the speed but you can use SpeedOn procedure by Kenneth Hobs below.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035

put below in a separate code modüle



Option Explicit

Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
glb_origCalculationMode = Application.Calculation
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
.Cursor = xlWait
.StatusBar = StatusBarMsg
.EnableCancelKey = xlErrorHandler
End With
End Sub

Sub SpeedOff()
With Application
.Calculation = glb_origCalculationMode
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.CalculateBeforeSave = True
.Cursor = xlDefault
.StatusBar = False
.EnableCancelKey = xlInterrupt
End With
End Sub



then



Sub vbax_54349_MinIf_in_VBA()

Dim rng1 As Range, rng2 As Range
Dim LastRow As Long

SpeedOn

With Worksheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rng1 = .Range("A2:A" & LastRow)
Set rng2 = .Range("D2:D" & LastRow)

For i = 2 To LastRow
If .Range("D" & i).Value = Evaluate("=MIN(IF(" & rng1.Address & "=" & .Range("A" & i).Address & "," & rng2.Address & "))") Then
.Range("A" & i & ":D" & i).Interior.Color = vbYellow
End If
Next i
End With

SpeedOff

End Sub

[shan]

Thank you.

So I need to keep this speed on and off code in separate module not with the actual code .. is it?

[mancubus]

you can. it will work.

these statements must be at top of the module as you may know:


Option Explicit

Public glb_origCalculationMode As Integer


it's about design.
i find it useful to keep udf's and called subs in separate modules. this enables easy copy of the module to another Project as well.

[Bob Phillips]

Thank for your Sir.

The formula is not giving me a required output.

Also, I need the ranges to be dynamic...

It would help to tell us in what way it does not work, and what YOU mean by dynamic in regard to your data. AN example workbook would help.

[shan]

Thank you for your reply.

When I was using the formula provided by you in Conditional Formatting it was not highlighting anything. Dynamic ranges, I mean to say this the rows may increase or decrease.

The formula is useful when used as a array ... it is giving us the correct result.

Mancubus had given me macro code incorporating the same formula for dynamic ranges and it is working fine.

Thank you both of you to assist me.

Have a great day ahead!!