How tochange color of MINIF cell ?
 

Hi!
I have one table, and need some vba which will find the min value (but
value <0) in each row of table and change interior color of this cells.

-
Thanks a lot,
MM

How tochange color of MINIF cell ?
 

You could do this with conditional formatting and skip the code.

for code

dim mMin as double, rng as Range
Dim cell as Range
for each rw in Range("Table").Rows
mMin = -10
set rng = Nothing
for each cell in rw.cells
if isnumeric(cell) then
if cell.Value mMin and cell.Value < 0 then
set rng = cell
mMin = cell.value
end if
End if
Next
if not rng is nothing then
rng.Interior.ColorIndex = 6
end if
Next

--
Regards,
Tom Ogilvy

"Mima" wrote in message
...

Hi!
I have one table, and need some vba which will find the min value (but
value <0) in each row of table and change interior color of this cells.

-
Thanks a lot,
MM

How tochange color of MINIF cell ?
 

Thx you Tom. Your code find me maximum value (I need minimum).

--
Regards, MM


"Tom Ogilvy" wrote in message
...
You could do this with conditional formatting and skip the code.

for code

dim mMin as double, rng as Range
Dim cell as Range
for each rw in Range("Table").Rows
mMin = -10
set rng = Nothing
for each cell in rw.cells
if isnumeric(cell) then
if cell.Value mMin and cell.Value < 0 then
set rng = cell
mMin = cell.value
end if
End if
Next
if not rng is nothing then
rng.Interior.ColorIndex = 6
end if
Next

--
Regards,
Tom Ogilvy

"Mima" wrote in message
...

Hi!
I have one table, and need some vba which will find the min value (but
value <0) in each row of table and change interior color of this cells.

-
Thanks a lot,
MM

How tochange color of MINIF cell ?
 

Tom,

Your code delivers MAXIMUM values in each row as tested against th
following sample data in a1:d4

A B C D
-5 3 -1 15
10 7 -4 -3
15 -9 5 -8
-8 -9 10 2

Sub mm()
Dim mMin As Double, rng As Range
Dim cell As Range
For Each rw In Range("a1:d4").Rows
mMin = -10
Set rng = Nothing
For Each cell In rw.Cells
If IsNumeric(cell) Then
If cell.Value mMin And cell.Value < 0 Then
Set rng = cell
MsgBox rng.Address
mMin = cell.Value
End If
End If
Next
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 6
End If
Next


Davi

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=48483

How tochange color of MINIF cell ?
 

Whoops:

dim mMin as double, rng as Range
Dim cell as Range
Range("Table").Interior.colorIndex = xlNone
for each rw in Range("Table").Rows
mMin = Application.Max(rw)
set rng = Nothing
for each cell in rw.cells
if isnumeric(cell) then
if cell.Value < mMin and cell.Value < 0 then
set rng = cell
mMin = cell.value
end if
End if
Next
if not rng is nothing then
rng.Interior.ColorIndex = 6
end if
Next

--
Regards,
Tom Ogilvy

"Mima" wrote in message
...
Thx you Tom. Your code find me maximum value (I need minimum).

--
Regards, MM


"Tom Ogilvy" wrote in message
...
You could do this with conditional formatting and skip the code.

for code

dim mMin as double, rng as Range
Dim cell as Range
for each rw in Range("Table").Rows
mMin = -10
set rng = Nothing
for each cell in rw.cells
if isnumeric(cell) then
if cell.Value mMin and cell.Value < 0 then
set rng = cell
mMin = cell.value
end if
End if
Next
if not rng is nothing then
rng.Interior.ColorIndex = 6
end if
Next

--
Regards,
Tom Ogilvy

"Mima" wrote in message
...

Hi!
I have one table, and need some vba which will find the min value (but
value <0) in each row of table and change interior color of this
cells.

-
Thanks a lot,
MM

How tochange color of MINIF cell ?
 

thanks for the heads up.
See corrected post in another leg of this thread.

--
Regards,
Tom Ogilvy


"davidm" wrote in
message ...

Tom,

Your code delivers MAXIMUM values in each row as tested against the
following sample data in a1:d4

A B C D
-5 3 -1 15
10 7 -4 -3
15 -9 5 -8
-8 -9 10 2

Sub mm()
Dim mMin As Double, rng As Range
Dim cell As Range
For Each rw In Range("a1:d4").Rows
mMin = -10
Set rng = Nothing
For Each cell In rw.Cells
If IsNumeric(cell) Then
If cell.Value mMin And cell.Value < 0 Then
Set rng = cell
MsgBox rng.Address
mMin = cell.Value
End If
End If
Next
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 6
End If
Next


David


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=484834