I see why neither your nor my code works. If the cells in columns O & Q have
formulas, the change event won't get triggered, we would need some kind of
Calculate event.
--
Gary''s Student - gsnu200832


"Suzanne" wrote:

Didn't work. Details I didn't include in the original message (if it helps):

Option Explicit

COL O is a dropdown listing hazard codes
=IF(P2="",HAZCODELIST,INDEX(HAZCODE,MATCH(P2,HAZCO DE,0)))
COL P is a dropdown listing the name of the hazard -- based on COL O
selection
=OFFSET(HAZCODE,MATCH(O2,HAZCODE,0)-1,1,COUNTIF(HAZCODE,O2),1)

COL Q is a dropdown listing hazard names (to search by name instead of code)
=IF(R2="",HAZNAME2,INDEX(HAZNAME2,MATCH(R2,HAZNAME 2,0)))
COL R is a dropdown of hazard codes -- based on COL Q selection
=OFFSET(HAZNAME2,MATCH(Q2,HAZNAME2,0)-1,1,COUNTIF(HAZNAME2,Q2),1)

If users select COL O, COL P populates with names associated with the
selection (and so on with COL Q/R)

What I need to do: Eliminate the opportunity for population of both COL O/P
and COL Q/R (it MUST be one or the other)

Finally, this wasn't possible in Excel 2003 -- can it be done in 2007??

Clear COL P if COL O changes (same for COL Q/R)

For example, if the cell in COL P is filled, users must manually clear
(delete) the cell content if they want to change the selection from COL O

The kicker... I don't want COL P to clear unless COL O actually
changes (i.e., don't clear on focus; do clear on change)

Thanks -- Suzanne

"Gary''s Student" wrote:

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set rr = Union(Range("O:O"), Range("Q:Q"))
If Intersect(t, rr) Is Nothing Then Exit Sub
c = t.Column
r = t.Row
Application.EnableEvents = False
If c = 15 Then
Cells(r, "P").ClearContents
Cells(r, "Q").ClearContents
Cells(r, "R").ClearContents
Else
Cells(r, "P").ClearContents
Cells(r, "O").ClearContents
Cells(r, "R").ClearContents
End If
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200832


"Suzanne" wrote:

If a cell in COL O changes, I want to clear the contents of COL P, Q R (in
the same row)
If a cell in COL Q changes, I want to clear the contents of COL O, P, R
(again in the same row)

I think I had the following working in Excel 2003, but its not working in
2007.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target
Select Case .Column
Case 15 'column O
Application.EnableEvents = False
Cells(.Row, "P").ClearContents
Cells(.Row, "Q").ClearContents
Cells(.Row, "R").ClearContents
Application.EnableEvents = True
Case 17 'column Q
Application.EnableEvents = False
Cells(.Row, "O").ClearContents
Cells(.Row, "P").ClearContents
Cells(.Row, "R").ClearContents
Application.EnableEvents = True
End Select
End With

End Sub