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



    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

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

  • Hi hesham


    Try this:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    On Error Resume Next
    For Each aCell In Sheet3.Range("E32:E1800")
    If aCell.Interior.ColorIndex <> -4142 And aCell.Interior.ColorIndex <> 3 Then
    aCell.Interior.ColorIndex = -4142
    End If
    If aCell.Value <= 2 And aCell.Value <> "" And aCell.Interior.ColorIndex <> 3 Then
    aCell.Interior.ColorIndex = 3
    MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
    MsgBox aCell.Address
    End If
    Next aCell

    End Sub


    The first loop changes all cell colours which are not red (ColorIndex =3) to 'No Fill'.
    The second loop checks if each aCell is <=2 AND IS NOT BLANK and is not already coloured red. I think that was what your code didn't check.
    You can alter these conditions to suit your needs.


    Note: You'll see more than one condition is checked by the If. . . And . . . steps


    Note: you could also do this using:


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E32:E1800")) Is Nothing Then
    If Target.Interior.ColorIndex <> -4142 And _
    Target.Value <= 2 And Target.Value <> "" Then
    Target.Interior.ColorIndex = 3
    MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
    MsgBox Target.Address
    End If
    End If


    End Sub


    This has no code to reset all the cells ColorIndex = -4142. Give it a try.


    Cheers


    KiwiSteve

  • Thank u


    Hi All


    I appreciate your fast reply....................


    Thank u all


    yours
    hesham

    ******************
    [SIZE=6]Yours
    h [/SIZE]

    Your Help Is Highly Appreciated

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!