changing cells based on dates

[bartoni]

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]

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]

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]

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]

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

[bartoni]

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]

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]

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]

Use <= rather than <.

:)