excel - Identify cells in red

Question

The title may be typically simple, but I did not get a solution yet even after researching over several forums on the net.

Let me explain the problem.

I have an excel workbook where few columns uses if conditions that refers other cells. Based on the cell's content, the back color of the cell is defined. For eg. If a cell is blank, it automatically changes to red. These red cells indicates missing information. So I am trying to design a macro to identify these red cells and get the address of each red cell.

For this I used the code for testing, MsgBox IIf(ActiveCell.Interior.ColorIndex = 3, "Yes", "No")

But this does not work. The reason is, the macro identifies the color index as -4142 irrespective of what color it is. Whether it is white or blue or red, it still shows -4142.

When I asked this question in a forum, I was redirected to this page.

http://www.cpearson.com/excel/CFColors.htm

I tested the functions given in that page. It works fine for the sample workbooks I created. But not in the workbook that I am trying to validate.

Could you please help me?

Answer 1

Using the following works for me where I made a few cells in C default red.

Dim ws As Worksheet
Set ws = Sheets("sheet1")
Dim i As Integer
i = 1
Do Until i = 11
    If ws.Range("C" & i).Interior.Color = RGB(255, 0, 0) Then
        debug.print "C" & i & "  is red!!"
    End If
i = i + 1
Loop

Edit:

This also works using colorindex = 3 I think your problem is a combination of activecell and IIf