min value with format

**giant** · 03-17-2010, 08:05 PM

hi there,
I have numbers in column a3 to a24.when i press a button i want the min number to turn red.any help would be great.

**giant** · 03-17-2010, 08:09 PM

hi there,
I have numbers in column a3 to a24.when i press a button i want the min number to turn red.any help would be great.All the numbers are decimal values.Thanks

**martindwilson** · 03-17-2010, 09:20 PM

try

Code:

Sub colour()
Dim mycell As Double
mycell = Application.WorksheetFunction.Min(Range("A3:A24"))
For Each c In Range("A3:A24")
If c = mycell Then c.Interior.ColorIndex = 3
Next c

End Sub

**giant** · 03-18-2010, 05:46 PM

martindwilson thank you works perfect.The numbers in the cells are constantly changing with each button click.So is it possible when i click again to make last red cell go back to normal and then next min number to color.

**martindwilson** · 03-18-2010, 05:55 PM

ok try

Code:

Sub colour()
Dim mycell As Double
mycell = Application.WorksheetFunction.Min(Range("A3:A24"))
For Each c In Range("A3:A24")
c.Interior.ColorIndex = xlNone
If c = mycell Then c.Interior.ColorIndex = 3
Next c

End Sub