PDA

View Full Version : Solved: Set Range depending on font colour



jazznaura
02-24-2010, 06:24 PM
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,

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

Bob Phillips
02-25-2010, 02:37 AM
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
02-25-2010, 02:43 AM
thanks xld, will give that a try.

jazznaura
02-25-2010, 12:59 PM
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.

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

SamT
02-25-2010, 04:15 PM
Smallest = WorksheetFunction.Small(RR, 1)
For Each C In RR.Cells
If C.Value = Smallest Then

Bob Phillips
02-25-2010, 04:46 PM
Not tested, but I was meaning something like this



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

jazznaura
02-26-2010, 05:28 AM
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
02-26-2010, 07:36 AM
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