Consulting

Results 1 to 9 of 9

Thread: changing cells based on dates

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location

    changing cells based on dates

    I have 3 probabilities and associated dates in columns H,K,N. The following rules should take place - If a cell in column H is 2005, a date in a cell in column K should be greater than column H and following on N should be greater than K (and H) .e.g

    2005 2006 2007 = correct
    2005 2002 2008 = incorrect
    2005 2006 2006 = incorrect

    How would I write a macro to be able to execute a pop up box if this rule hasnt been followed. I use data validation in these columns by making a custom list i dont know if this helps. I thought it would be easier to create 2 VBA codes - 1 to compare columns K and H and one to compare columns K and N.

    Ive had a go but its not working

    Private Sub worksheet_CHECKdates()
    For MY_ROWS = 2 To Range("H65536").End(xlUp).Row
        If Range("H" & MY_ROWS).Value > Range("K" & MY_ROWS).Value Then
            MY_CHOICE = MsgBox("date error found in column H/K")
        End If
    End If
    Next MY_ROWS
    End Sub
    Any ideas?
    Thanks
    Last edited by Aussiebear; 04-29-2023 at 09:44 PM. Reason: Adjusted the code tags

  2. #2
    BoardCoder
    Licensed Coder
    VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    You could just use a worksheet formula to check. Using VBA though I don't see a problem with your code other than the extra end if. You could make it check both columns in one loop though using:

    Private Sub worksheet_CHECKdates() 
        For MY_ROWS = 2 To Range("H65536").End(xlUp).Row 
        If Range("H" & MY_ROWS).Value > Range("K" & MY_ROWS).Value Then 
            MY_CHOICE = MsgBox("date error found in column H/K") 
        End If 
        If Range("N" & MY_ROWS).Value > Range("H" & MY_ROWS).Value Then 
            MY_CHOICE = MsgBox("date error found in column N/H") 
        End If 
    Next MY_ROWS 
    End Sub
    Last edited by Aussiebear; 04-29-2023 at 09:45 PM. Reason: Adjusted the code tags
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    Have you tried it? I thought it would work but in my worksheet it doenst do anything if i put in erroneous values. Does it matter what my worksheet is called?

  4. #4
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Works for me.

    The worksheet can be any name but must be the activesheet when you run the code.

    Additionally, I just noticed (I'd copied and pasted before) that you declared the function private. Remove the word private from the start. You'll then be able to run it by going to tools>macros>Macros.. selecting it and clicking run.

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

  5. #5
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    I also just noticed I had this the wrong way round:

    If Range("N" & MY_ROWS).Value > Range("H" & MY_ROWS).Value Then 
        MY_CHOICE = MsgBox("date error found in column N/H") 
            End If
    should be:

    If Range("K" & MY_ROWS).Value > Range("N" & MY_ROWS).Value Then 
        MY_CHOICE = MsgBox("date error found in column K/N") 
            End If
    Last edited by Aussiebear; 04-29-2023 at 09:46 PM. Reason: Adjusted the code tags
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  6. #6
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    OK ive got it to work by running it as you suggested but i wanted it to run automatically. So when a user inputs dates in the relevant columns the msg box comes up without having to run any macros. i.e using a worksheet event ( the private macro bit). I think its got something to do with the type of event ive used......

  7. #7
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Ah ok. In that case you need to look at the worksheet_change evnt. You've actually made up and event. The events available for the worksheet object can be found by seleceting the worksheet in the top left dropdown of the codewindow. The evnts will then be listed in the top right drop down.

    On selecting the change event the stub will be created for you.

    You could then use soemthing like:


    if target.column=11 then 'i.e. column K
        if range("H" & target.row)<>"" then 'column H isn't blank
            if target.value<range("H" & target.row).value then msgbox "Should be a value greater than column H"
       end if
    end if

    and similar for column N.

    Last edited by Aussiebear; 04-29-2023 at 09:47 PM. Reason: Adjusted the code tags
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  8. #8
    VBAX Regular
    Joined
    Jul 2004
    Posts
    16
    Location
    Excellent. One thing though, how do I get the macro to put up an error if cells H and K are equal - at the moment it only errors if K is less than H and not equal to H.

    Many thanks for your help!!

  9. #9
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Use <= rather than <.

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

Posting Permissions

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