excluding a value in a max determination

[Guest]

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

 

[Kevin Beckham]

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?

 

[Guest]

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

Thank you agai
Schwartz

 

[Bob Phillips]

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)