VBA to identify cells in red

ramkumarcn

New member
Joined
Apr 19, 2013
Messages
5
Reaction score
0
Points
0
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?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
What version of Excel do you have?

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

ramkumarcn

New member
Joined
Apr 19, 2013
Messages
5
Reaction score
0
Points
0
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?
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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?
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
If you are in 2010:
Code:
MsgBox IIf(ActiveCell.DisplayFormat.Interior.ColorIndex = 3, "Yes", "No")

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

ramkumarcn

New member
Joined
Apr 19, 2013
Messages
5
Reaction score
0
Points
0
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
 
Top