excluding a value in a max determination
 

Hi

I want to determine a max value in a range: =MAX(A1:A3

But if one of the cells has this format

With Selection.Interio
.ColorIndex =
.Pattern = xlLightU
.PatternColorIndex = 2
End Wit

I want to exclude it from the max value determination. Can anybody help me out with a piece of code that returns the correct max value

Thank yo
Schwartz

excluding a value in a max determination
 

not elegant but...

first = True
For Each c In myRange
With c.Interior
If .ColorIndex < 2 Or .Pattern < xlLightUp Or _
.PatterColorIndex < 22 Then
If first Then
max = c.Value
first = False
ElseIf c.Value max Then
max = c.Value
End If
End If
End With
Next c
MsgBox "Max = " & max


Kevin Beckham

-----Original Message-----
Hi

I want to determine a max value in a range: =MAX(A1:A3)

But if one of the cells has this format:

With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = 22
End With

I want to exclude it from the max value determination.
Can anybody help me out with a piece of code that returns
the correct max value?

Thank you
Schwartz
.

excluding a value in a max determination
 

Hey, it works for me - and there is no points for style..

Thank you agai
Schwartz

excluding a value in a max determination
 

I turned this into a UDF (just for the hell of it), to give flexibility, in
case Dr Schwartz has many such tests (sic!).

Function OddMax(rng As Range, _
Optional ColorIndex As Long = -1, _
Optional Pattern As Long = -1, _
Optional PatternColorIndex As Long = -1) As Range
Dim c As Range
Dim first As Boolean
Dim oRng As Range
first = True
For Each c In rng
With c.Interior
If ((ColorIndex = -1 Or .ColorIndex < ColorIndex) And _
(Pattern = -1 Or .Pattern < Pattern) And _
(PatternColorIndex = -1 Or .PatternColorIndex <
PatternColorIndex)) Then
If first Then
Set oRng = c
first = False
Else
Set oRng = Union(oRng, c)
End If
End If
End With
Next c
Set OddMax = oRng

End Function

Use like so

=MAX(oddmax(I1:I3,6)) ' justs excludes a colour

=MAX(oddmax(I1:I3,6,17)) ' excludes a colour or a pattern

=MAX(oddmax(I1:I3,6,17,7)) ' all 3

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Kevin Beckham" wrote in message
...
not elegant but...

first = True
For Each c In myRange
With c.Interior
If .ColorIndex < 2 Or .Pattern < xlLightUp Or _
.PatterColorIndex < 22 Then
If first Then
max = c.Value
first = False
ElseIf c.Value max Then
max = c.Value
End If
End If
End With
Next c
MsgBox "Max = " & max


Kevin Beckham

-----Original Message-----
Hi

I want to determine a max value in a range: =MAX(A1:A3)

But if one of the cells has this format:

With Selection.Interior
.ColorIndex = 2
.Pattern = xlLightUp
.PatternColorIndex = 22
End With

I want to exclude it from the max value determination.
Can anybody help me out with a piece of code that returns
the correct max value?

Thank you
Schwartz
.