how do i ignore cells with font color?

[chick-racer]

I just cannot get my code to do what i want. I hope someone can help
me.

I am trying to get it to loop through all the values in the range and
color the font of certain low values. The way i think it would work
best is if it could just ignore the values that it already has colored
because as it stands, i can only get it to see one low value per
range.

my question is: how can i get it to ignore font without the default
black? for example blue or red font.

here is a snippet of code for that portion of the program.. MUCH thanks
for your assistance, because right now it's in an endless loop. AHHHH!

grubb = Cells(J + K, "V").value
tlow = Cells(J + K, "T").value
thigh = Cells(J + K, "U").value

While tlow > grubb
'code to ignore cells with color font color other than
default black since i would like it to color all the tlow values
greater than the grubb value.
If ActiveCell.Font.ColorIndex <> xlAutomatic And
ActiveCell.Font.ColorIndex <> 1 Then

Set rng = Range("D" & J + K, "N" & J + K)
lowval = Application.WorksheetFunction.Min(rng)
result = Application.Match(lowval, rng, 0)
If Not IsError(result) Then rng(1,
result).Font.Color = RGB(0, 10, 200)
minValue =
Application.WorksheetFunction.Min(Range(rng)) 'should return minimum
value without the cells with colored font
tlow = (Cells(J + K, "R").value - minValue) /
(Cells(J + K, "S").value) 'calc new tlow for the "when" condition
End If
Wend

 

[Tushar Mehta]

See if this page by Chip Pearson helps:
http://www.cpearson.com/excel/colors.htm

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[chick-racer]

thank you, i have read that information before... but, i'm still stuck.
I just dont know what else to do!

any one have ideas????

 

[Tushar Mehta]

I don't understand what you are trying to do, but here's something that
should help you get going.

Enter the following function in a standard module (watch out for word
wrap). It is based on one of Chip's examples.

Option Explicit

Function ValuesWithColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Variant
Dim Rng As Range, Rslt As Variant, I As Long
ReDim Rslt(1 To InRange.Cells.Count)

Application.Volatile True
I = 1
For Each Rng In InRange.Cells
If OfText = True Then
Rslt(I) = IIf(Rng.Font.ColorIndex = WhatColorIndex,
Rng.Value, "")
Else
Rslt(I) = IIf(Rng.Interior.ColorIndex = WhatColorIndex,
Rng.Value, "")
End If
I = I + 1
Next Rng
On Error GoTo Huh
If Application.Caller.Columns.Count > 1 Then
ValuesWithColor = Rslt
Else
ValuesWithColor = Application.WorksheetFunction.Transpose(Rslt)
End If
Exit Function
Huh: ValuesWithColor = Rslt
End Function

Use the above function in a worksheet formula as
=MIN(ValuesWithColor(A1:A4,-4105,TRUE))
or
=SUM(ValuesWithColor(A1:A4,-4105,TRUE))

or, in VBA as
Sub testIt()
MsgBox Application.WorksheetFunction.Min( _
ValuesWithColor(Range("a1:a4"), _
xlColorIndexAutomatic, True))
End Sub
Note that -4105 is the numeric value of xlColorIndexAutomatic.

--
Regards,

Tushar Mehta
MS MVP Excel 2000-2003
www.tushar-mehta.com
Excel, PowerPoint, and VBA tutorials and add-ins
Custom Productivity Solutions leveraging MS Office