Results 1 to 7 of 7

Thread: VBA to identify cells in red

  1. #1

    VBA to identify cells in red

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    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?

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    What version of Excel do you have?

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

  3. #3

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


    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?

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    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?

  5. #5
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Excel Version
    If you are in 2010:
    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.
    This website wants to know your momentum - | Deny | | Allow |

  6. #6


    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.


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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts