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
    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
    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