Solved: Set Range depending on font colour

[jazznaura]

Hi all,
Is there a way to SET a range depending on font color ?
I jusy want to work with cells in the range with font color green.
What I got so far below. HELP!
Thanks,
[vba]
Set BigRange = Range("q1:q20").Font.ColorIndex = 4 ‘ this is the problem
If Range("D34").Value < 1 Then
For Each C In Range("BigRange ").Cells
If C.Value = WorksheetFunction.Small(Range("BigRange "), 1) Then
Cells(C.Row, C.Offset(, -13).Column).Value = "YES"
Range(C, C.Offset(, 13)).Font.ColorIndex = 3
Exit For
End If
Next C
End If
[/vba]

[Bob Phillips]

You would have to loop through the range, test the colour, and union all cells with that colour.

As you loop through BigRange later, why not just loop the original range, and add a test before you set the value?

[jazznaura]

thanks xld, will give that a try.

[jazznaura]

Here’s what i came up with. its the best i could come up with, with my knowledge.
If anyone has any ideas as to how to make it more efficient, please let me know.

[vba]If Range("D34").Value < 1 Then
For Each C In Range("Q9:Q29").Cells
If C.Font.ColorIndex = 4 Then
If RR Is Nothing Then
Set RR = C
Else
Set RR = Application.Union(RR, C)
End If
End If
Next C

For Each C In RR.Cells
If C.Value = WorksheetFunction.Small(RR, 1) Then
Cells(C.Row, C.Offset(, -13).Column).Value = "YES"
Range(C, C.Offset(, 13)).Font.ColorIndex = 3
Exit For
End If
Next C
End If[/vba]

[SamT]

[vba]
Smallest = WorksheetFunction.Small(RR, 1)
For Each C In RR.Cells
If C.Value = Smallest Then
[/vba]

[Bob Phillips]

Not tested, but I was meaning something like this

[vba]

For Each C In Range("q1:q20")

If .Font.ColorIndex = 4 Then

If C.Value = WorksheetFunction.Min(Range("q1:q20")) Then

C.Offset(, -13).Value = "YES"
C.Offset(, -13).Font.ColorIndex = 3
Exit For
End If
End If
Next C
[/vba]

[jazznaura]

thanks for the update, i'll have a look at both suggestions,

xld your code checks if value is smallest in the whole range, but i'm looking for the smallest of the green font values.

i'll have a play see what i can come up with.

thanks again,

[SamT]

[vbA]
If Range("D34").Value < 1 Then
For Each C In Range("Q9:Q29").Cells
If C.Font.ColorIndex = 4 Then
If RR Is Nothing Then
Set RR = C
ElseIf
RR.Value > C.Value
Set RR = C
End If
End IF
Next C

RR.Offset(, -13).Value = "YES"
RR.Offset(, 13).Font.ColorIndex = 3
[/vba]