Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells.
The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on the worksheet, and then using Excel FIND function we will find which cell contain that minimum/ maximum value.
Excel VBA macro implementation of the algorithm above will look like below, change code Application.Min(oRg) into Application.Max(oRg) to find the maximum value instead of minimum value.
Sub FindMinValue()
Dim oRg As Range, iMin As Variant
Set oRg = Cells
'Finding the minimum value
'change Application.Min(oRg) into Application.Max(oRg) to find the maximum value
iMin = Application.Min(oRg)
'Select cell containing the min value
oRg.Find(What:=iMin, _
After:=oRg.Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False _
).Select
'Change selected cell format
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
.PatternTintAndShade = 0
End With
'Displaying min value info
With Selection
MsgBox "Min value : " & iMin & vbCrLf & _
"Cell position " & vbCrLf & _
"Row : " & .Row & vbCrLf & _
"Column : " & .Column
End With
End Sub
As an additional info, the code above will display the cell info, row and column position of the cell containing the minimum/maximum value.
Once more, the Internet saves me !
I was having the same problem, googled it, solved it, all in 3 minutes, thanks to all the good folks who raise questions, and the even better souls who answer them on forums.
Thanks a lot everyone
This really helped! Thanks a ton for this!
Hello there,
When I run this code, I get Runtime Error 91 (object variable or with variable not set) and when I debug it refers to the Find function.
Does anyone know what might cause this?
Hi Jenn, maybe not the Find function that wasn’t set, but oRg object. Did you already set the oRg range object with “Set oRg = Cells” like in line 5?
Pingback: M?nimo de un rango de celdas - Ayuda Excel
Pingback: Angry Birds Online
Pingback: reverse mortgages calculator
Pingback: Alternative Energy Projects
Pingback: walmart credit card
Pingback: debt collection agency
Pingback: Wedding photographer services calgary
Pingback: collection agency rates
Pingback: film x
Pingback: best bread machine
Pingback: rapidrecoverysolution.com
Pingback: collection agency newsletter
Pingback: uverse coupon,uverse deals, att uverse coupon,uverse coupon code internet
Pingback: vimax
Pingback: wedding photographer calgary
Pingback: buikspieroefeningen
Pingback: stretcher
Pingback: juegos de ben 10
Pingback: patio table umbrellas
Pingback: Tucson Assisted Living
Pingback: ???? ???????????
Pingback: hier
Pingback: Quality Backlinks
Pingback: ????????????? ???
Pingback: best buy coupon codes
Pingback: Scrapeboard
Pingback: ???????? xis
Pingback: free ipad 2
Pingback: window shutters
Pingback: jualan online
Pingback: tricksterzz
Pingback: coupons for laundry detergent
Pingback: Marijuana Graines À Autofloraison
Pingback: fapturbo reviews
Pingback: ????? ??????
Pingback: ????? ??????
Pingback: Collection Agency Laws
Pingback: pay per click
Pingback: Vippi pankkitunnuksilla
Pingback: http://freecreditreportz.com
Pingback: europeana portal
Pingback: how to get a free credit report
Pingback: Cityville hack
Pingback: is a used car warranty worth it
Pingback: Document Solutions