Results 1 to 4 of 4

Worksheet change not recognising target cell changes

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,398

    Worksheet change not recognising target cell changes

    Apologies if this has been covered previously, but have been unable to find a solution.

    Col A on RANGES sheet lists three different meals. Cols B - D list the ingredients of each one.

    Macro should be triggered if the DropList in C6 on the CALCULATOR sheet changes.
    If the User clears the cell, it should delete any ingredients listed in Col D and exit the sub.
    If the User selects a meal from the C6 Droplist, Macro should delete any ingredients in Col D, find the relevant list on the RANGES sheet, and copy those ingredients back to Col D on the CALCULATOR.

    For whatever reason, the Macro is not "seeing" any change to C6, so nothing happens if you change or clear it.

    Option Explicit
    
    Dim c As Long, f As Long, m As Long
    Dim rng As Range
    Dim ws As Worksheet
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Set ws = Sheet1
            
    'Run Macro only if C6 changes
        
        With ws
        If Target.Address = .Range("C6") Then
        .Range(.Cells(6, 4), .Cells(23, 4)).ClearContents
        
        
    'Stop macro if no meal selected
        
        If .Range("C6") = "" Then
        Exit Sub
        Else
         
        Application.EnableEvents = False
         
        With Sheet2
            
    'Find last col on Ranges sheet
    
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column
        
    'Find col matching Meal
            
            With .Range("1:1")
            Set rng = .Find(What:=ws.Range("C6"), After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
             
                If Not rng Is Nothing Then
                m = rng.Column
                End If
            End With
                
    'Find last row in Meal column
        
                f = .Cells(.Rows.Count, m).End(xlUp).Row
        
    'Copy ingredients to CALCULATOR
        
                .Range(.Cells(2, m), .Cells(f, m)).Copy Destination:=Sheet1.Cells(6, 4)
        
           End With
        
        End If
        
        End If
        
        End With
        
        Application.EnableEvents = True
        
                    
        End Sub
    All solutions, suggestions and alternatives welcome as ever

    Ochimus
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code not recognising Target cell
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-23-2020, 11:08 AM
  2. [SOLVED] Trouble with Worksheet Change when Target Cell Changed by Formula
    By phelbin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2016, 04:00 PM
  3. Worksheet Change - need to ignore target cell in results
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-25-2015, 06:32 PM
  4. get cell value from worksheet change target
    By vangxbg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 06:07 PM
  5. Replies: 5
    Last Post: 01-22-2013, 11:38 AM
  6. Worksheet Change Target??
    By spinkung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2009, 05:33 AM
  7. Change the formula of a target cell in a self-defined worksheet function
    By mxcaocao in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2008, 02:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1