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?
Answer
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
Edit:
This also works using colorindex = 3 I think your problem is a combination of activecell and IIf