2

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?

Teamothy
  • 2,000
  • 3
  • 16
  • 26
user2341632
  • 49
  • 1
  • 3
  • 9
  • Can you upload the file and give the link. – Santosh May 02 '13 at 05:16
  • The functions from the cPearson link only handle certain types of conditional formats (you could check the Imediate window in the VBA IDE to see if they are reporting any errors). Please update your question with full details of the CF you are using to colur your cells. – chris neilsen May 02 '13 at 05:24
  • Actually, only one column has a formula =IF(AND(D5=""),"1", IF( OR(AX5:BO5 ),"2",IF(AND(D5<>""),"3","0"))). And I am not sure whether other columns have conditional formatting enabled. Because the excel workbook was created by someone and he is not available. I think these columns are activated with data validation. Because when I remove the cell's content it automatically becomes red. Does this help? – user2341632 May 02 '13 at 06:52

1 Answers1

5

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

enter image description here

Edit:

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

Alistair Weir
  • 1,809
  • 6
  • 26
  • 47