PDA

View Full Version : changing cells based on dates



bartoni
07-13-2004, 03:12 AM
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

mark007
07-13-2004, 03:20 AM
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

:)

bartoni
07-13-2004, 03:26 AM
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?

mark007
07-13-2004, 03:33 AM
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.

:)

mark007
07-13-2004, 03:37 AM
I also just noticed I had this the wring 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

bartoni
07-13-2004, 03:39 AM
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......

mark007
07-13-2004, 03:55 AM
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.

:)

bartoni
07-13-2004, 04:06 AM
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!!

mark007
07-13-2004, 04:11 AM
Use <= rather than <.

:)