If Font.ColorIndex = 5 then . . .

[Guest]

I have a column of data where some of the cells font color is blue and some
is black. I need a macro which looks to see if the font color is blue, then
put a certain text string in another column.

For example: if cell C45 Font.ColorIndex = 5, then put "Embedded" in cell
M45, if not then put "Loose" in cell M45. And so on all the way through the
data. My goal is to be able to filter column M for "Embeded" after I run the
macro.

Can anyone help?

 

[Guest]

I was unclear as to whether you wanted to search Column C or just what so
this searches the entire used range. If you want just column C I comented out
that line. You can just Un-comment it. It adds the word embedded. It does not
add the word Loose but if all you want to do is filter then this should work
for you...

Sub AddFilterField()
Dim rngCurrent As Range
Dim rngToSearch As Range
Dim rngToFill As Range

Set rngToFill = ActiveSheet.Range("M1").EntireColumn
Set rngToSearch = UsedRange
'Set rngToSearch = Intersect(ActiveSheet.Range("C1").EntireColumn,
UsedRange)

For Each rngCurrent In rngToSearch
If rngCurrent.Font.ColorIndex = 5 Then
Intersect(rngCurrent.EntireRow, rngToFill).Value = "Embedded"
End If
Next rngCurrent
End Sub

HTH

 

[Bob Phillips]

This page shows how to filter by colour
http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

RP
(remove nothere from the email address if mailing direct)