PC Review


Reply
Thread Tools Rate Thread

Dependent Cell - Change Value Problem

 
 
max
Guest
Posts: n/a
 
      5th Jun 2009
I have a Change Event procedure which will clear the active cell's
contents in the "INGREDIENTS" range if the "CATEGORY" target cell's
value is changed.
My problem occurs if the user should choose the same list value in the
active cell "CATEGORY" dropdown List
..
The code still clears the contents of the offset cell.
I would like a way to prevent this from happening.



On a "Recipe Sheet", I have two range columns: "CATEGORY" and
"INGREDIENTS",
which refer to the "CategoryColumn" and "IngredientsColumn" ranges on
the "IngredientsLists" Sheet.

The Validation list formula for the "CATEGORY" cells
"=CATEGORY_NAMES" which is the reference list of categories.

The Validation list formula for the "INGREDIENTS" cells
"=OFFSET(CategoryStart,MATCH($B1,CategoryColumn,0),1,COUNTIF
(CategoryColumn,$B1),1)"

========================================
CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Range("CATEGORY") Is Nothing Then Exit Sub
On Error GoTo 0

If Not Intersect(Target, Range("CATEGORY")) Is Nothing Then
Application.EnableEvents = False
Target.Offset(0, 1) = ""
Application.EnableEvents = True
End If

End Sub



Thanks Max
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell fill color dependent on formula result =?Utf-8?B?SGVsZW4=?= Microsoft Powerpoint 4 5th Sep 2007 05:44 PM
Another cell formatting dependent on cell contents question / message box popup? StargateFan Microsoft Excel Programming 2 14th Jan 2006 02:47 PM
Change Cell Color dependent on Cell Contents =?Utf-8?B?QmlsbA==?= Microsoft Excel Programming 4 15th Mar 2005 04:33 PM
Re: How do I change the colour of a cell dependent on a condition? JulieD Microsoft Excel Misc 1 15th Sep 2004 04:21 PM
Re: How do i change the graph source data to a variable dependent on the value in a cell Tom Ogilvy Microsoft Excel Programming 1 18th Oct 2003 04:27 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:25 PM.