Highlight max/min in excel 2007, VBA

Algae - Jan 25, 2011 at 03:11 AM
 Algae - Jan 27, 2011 at 06:56 PM

I'm quite new to Excel VBA and need some help doing the following.

I have a lot of data and need to highlight (or change the background colour) of the cell containing the maximum and minimum values in each column. My data range is from "D68:DI89". I know how to use conditional formatting and select each individual column, but it's taking too long and I've a lot more data sets that need the same treatment.

I found the following code online, but it can only pick up the maximum number in the entire sheet.

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 _

'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

Thanks in advance.

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 27, 2011 at 08:58 AM
I think you can use the conditional formatting to accomplish that too
I have tried that, but I have to do it column by column, manually. Doing that 120 times (I have 120 columns) for each set of data I have is extremely time consuming. I was just wondering if it is possible to use VBA with some loop code to accomplish the same thing.