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
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,
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.