Advice: Help with Msgbox

i did conditional formatting that if a cell value is <=2 change the color to purpel,
then after that did writ a code that makes a Msgbox appears as a wornning followed by another
Msgbox that give the exact addrress of the cell that contain <=2.....

i tried to with this code, but it did not work........
any hints is appreciated.............

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
'Dim myCheck As Integer
For Each c In Sheet3.Range("E32:E1800").Cells
    If c.Interior.ColorIndex <> -4142 Then
        If c.Value <= 2 Then
            c.Interior.ColorIndex = 3
            MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
            MsgBox c.Address
        End If
    End If
Next
End Sub

---

yours
hesham

Hi Hesham,

What color is this:

Quote:

---

If c.Interior.ColorIndex <> -4142 Then

---

Not sure what that's for, but what color are you looking for here before you check the value of each cell?

And you may want to check out the VBA tags when you post, it will make your code appear very legible, clear and concise. :)

-4142 is no colour, try using the constant xlNone instead or the hex equivalent: &HFFFFEFD2

Is this where the problem lies? What exactly was not working?

:)

Just as a side note, if you're going to adapt from your code, I'd put something like this in there right after your For Each ...:

Code:

---

For Each c In Sheet3.Range("E32:E1800").Cells
    If c.Interior.ColorIndex = 3 Then Exit Sub

---

Otherwise you're going to be firing it every time a cell is changed! If you have more than about 4 cells that meet your criteria, it'll get old fast! :yes

Hi helmekki,

Welcome to VBA Express!

I'm not too sure what the code is supposed to do; it is all subject to cells not having an interior colour set; if none of your cells are formatted (except conditionally) then no cell is going to pass the test and nothing will happen.

The Interior ColorIndex value is not affected by Conditional Formatting. You cannot check for colours set with Conditional Formatting in VBA code - what you must do is check whether the underlying condition is true or not (in this case cell value <=2). It is possible in code to examine the conditional format conditions but it is not easy to extract information from them in such a way as to test against a cell.

If I've misunderstood, please post back.

Good point Tony. Jack also has a very good point here: http://www.ozgrid.com/forum/showthread.php?t=20762