You are welcome! Thanks for the feedback!
Stefi
Clicking the YES button will be appreciated.


€žGazz_85€� ezt Ã*rta:

Cheers,

Works like a dream, your a real life saver!!

very much appreciated thanks for all yopur help

:-)

"Stefi" wrote:

This is a bad joke of this forums text editor: in case of long lines it
splits them up therefore copy/pasted them back into the VBA editor one VBA
line are split up in two without a line-continuation mark (" _" that's a
space and an underscore) at the end of the first part. In this case move all
Is Nothing _
to the end of the previous lines like follows:

If Not (Intersect(...) Is Nothing _
And Intersect(...) Is Nothing _
And Intersect(...) Is Nothing _
And Intersect(...) Is Nothing) Then

Regards,
Stefi


€žGazz_85€� ezt Ã*rta:

Thanks for the help,

sorry to be a pain but i'm getting a compile error: syntax error.

the bit it doesn't like is:

If Not (Intersect(Target, Columns(colpair1(0) & ":" & colpair1(1))) Is
Nothing _
And Intersect(Target, Columns(colpair2(0) & ":" & colpair2(1))) Is
Nothing _
And Intersect(Target, Columns(colpair3(0) & ":" & colpair3(1))) Is
Nothing _
And Intersect(Target, Columns(colpair4(0) & ":" & colpair4(1))) Is
Nothing) Then

any ideas???

cheers

"Stefi" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
colpair1 = Array("C", "D")
colpair2 = Array("J", "K")
colpair3 = Array("P", "Q")
colpair4 = Array("V", "W")
If Not (Intersect(Target, Columns(colpair1(0) & ":" & colpair1(1))) Is
Nothing _
And Intersect(Target, Columns(colpair2(0) & ":" & colpair2(1))) Is
Nothing _
And Intersect(Target, Columns(colpair3(0) & ":" & colpair3(1))) Is
Nothing _
And Intersect(Target, Columns(colpair4(0) & ":" & colpair4(1))) Is
Nothing) Then
colshift = IIf(Target.Column = Range(colpair1(0) & 1).Column Or _
Target.Column = Range(colpair2(0) & 1).Column Or _
Target.Column = Range(colpair3(0) & 1).Column Or _
Target.Column = Range(colpair4(0) & 1).Column, 1, -1)
othercol = Target.Column + colshift
If Not IsEmpty(Cells(Target.Row, othercol)) Then
MsgBox "Other column is not empty!", vbOKOnly, "Invalid input!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Regards,
Stefi


€žGazz_85€� ezt Ã*rta:

the worksheet is in 4 sections - representing the 4 different work areas we
have

Section 1 - columns C - I

Section 2 - columns J - O

Section 3 - columns P - U

Section 4 - columns V - Z

each section has the option of long or short term jobs with then the
validation of a drop down list containing types of job. so for each section i
need a seperate validation so for example in:

section 1 either column c or d

section 2 either column j or k

section 3 either column p or q

section 4 either column v or w

but you would still be able to enter in other sections if you entered in C,
but only not in D. if this makes sense???

Cheers


"Stefi" wrote:

I need it to work so that it work for column c &d so which bit would i
change??
also i need to do the same for columns j&k, columns p&q, and columns v&w can
i just replicate this code or do i need something a little more complex?

In the same worksheet?


also i just need it to validate seperate groups ie. c&d,j&k,p&q, and v&w so
if theres a value in c you CAN still put a value in j,ect but not in d....if
you know what i mean??

Do you mean "... but not in d, k, q, w"?

Stefi