VBA to identify cells in red

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 a webpage called 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?

What version of Excel do you have?

What conditions determine the colour (look at the CF formulae)?

Hi,

Thank you so much for responding. I have Excel 2010. Only one column in the worksheet has a formula

=IF(AND(D5=""),"1",
IF(
OR(AX5:BO5
),"2",IF(AND(D5<>""),"3","0")))

And the remaining columns does not have any formulas. Also I am not sure whether the remaining cells have conditional formatting enabled. I think the remaining columns are enabled with data validation techniques. Because when I remove the cell's content, it is getting highlighted in red.

Does this information help?

That was my fear, CF in Excel 2010 is much more varied that in 2003.

It would help to see the workbook, can you post it?

If you are in 2010:

This will not work in a UDF called from a cell but will in a macro.

Hi Bob,

Thank you so much for your help. I really appreciate for trying to help me out on this issue. Thanks a ton Bob.. I really mean it.

Hi JoePublic,

The code you gave me works perfect for me. You know one thing, I wasnt able to move to the next module without making the macro to recognize the cell's true color. You threw some light on this. Thanks Joe. I really cant explain how I was worried without being able to make it work. I am really delighted. Your code works perfect for me. Thanks a ton once again to you and Bob.

Regards,
Ram

I also learned a lot from what Bob and JoePulic said. Thank you, too.