Consulting

Results 1 to 6 of 6

Thread: Advice: Help with Msgbox

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    4
    Location

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


    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

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Hesham,

    What color is this:

    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.

  3. #3
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    -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?

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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 ...:


    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!

  5. #5
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Good point Tony. Jack also has a very good point here: http://www.ozgrid.com/forum/showthread.php?t=20762

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •