Excel: Find certain cells based on text colour. 1

[micang]

Hi All,

Excel 2003:

I have a list of text in a column. Some of these text values are in red colour.

I would like to identify these cells that have text in red, by putting a "1" in the adjacent cell.

Is this possible?

Many thanks

Michael

 

[royalcheese]

Very

You need a loop to go through your list and where the returned color is red then make the next row / column = 1

isred = 123 ' What ever color red is 

if IsRed = Cells(x, y).Interior.Color then
   'make next cell equal 1 
   Cells(x, y +1).value = 1
end if

 

[Fenrirshowl]

Michael

Try something along the lines of:

Sub see_red()
For Each cll In Selection
    If cll.Font.ColorIndex = 3 Then
        cll.Offset(0, 1) = 1
    End If
Next cll
End Sub

Highlight the column or the cells you want to check and then run the macro.

I find it easiest to use the macro recorder when performing the desired action (here, turning certain cells red) and then modifying the code.

Fen

 

[micang]

Fen,

Thank you so much, that works perfectly. Much appreciated.

Michael