Can I update the same text in a different column when i change column C?

[tonywatsonhelp]

Hi Everyone,
I have text in column C that becomes dropdown list for column F
the problem is if I see a mistake or want to chnage whats in a cell in column C it might have already been used in column F,
So is there a way that if I change whats in a cell in column C it could trigger a macro and also change the same wordings in column F

So if for example C7 said "Tottal Sales" and I changed it to "Total Sales" the macro would find all "Tottal Sales" down column F and change them as well?
It would only need to change when the entire cell matched.

Any Ideas please let me know,
thanks
Tony

 

[sijpie]

You have a problem here. When you make a change in a cell and press Enter to confirm, or click on another cell, Excel sends an event interrupt. This you can intercept and use to correct the values in column F. However, the old value of the cell in C is lost. So how does the macro know which value to look for?

The least complicated way is to check each value in column F against all allowed choices in column C. If the current value in F cannot be found in C, then it needs to be changed to the new value in C.

this code needs to be written in the worksheet code module using the Worksheet_Change() sub

What is the starting cell of the dropdown values in C? I will write you the code tomorrow or later today

 

[sijpie]

Here is the code. You have to paste this into the worksheet module:
Right click on the sheet tab in Excel. Select 'View Code'
This will open the VBA editor in the worksheet module. Paste the code there.

Read through the comments, particularly those starting with <<<<<<. You have to act here!!!!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vDD As Variant, vF As Variant, vC As Variant
    Dim lR As Long, lC As Long, UBc As Long
    Dim rDD As Range, rF As Range
    Dim bFnd As Boolean
    
    
    Set rDD = Range("C1")       '<<<<<<<< the start of your list for the dropdown. Modify address to suit <<<<<<<
        
    Set rDD = rDD.Resize(rDD.CurrentRegion.Rows.Count, 1)
    
    'check if changes made to this area, else quit. Also quit if more cells have been changed at same time (through paste)
    If Target.Cells.Count = 1 And Not Intersect(Target, rDD) Is Nothing Then
        'yes changes made to the list in column C
        ' load the value of the changed cell in a variable
        vC = Target.Value
        ' load the dropdown items list into an array for fast processinng
        vDD = rDD.Value
        UBc = UBound(vDD, 1) ' number of items in list
        
            Set rF = Range("F1") '<<<<<<<< the start of the table in column F to be checked. Modify address to suit <<<<<<<
            Set rF = rF.Resize(rF.CurrentRegion.Rows.Count, 1)
            
            ' load the list into an array for fast processinng
        vF = rF.Value
        
        ' now loop through each item in F to see if it appears in C. _
         By doing this with the copies held in the two arrays vDD and vF this is done lightning fast
        For lR = 1 To UBound(vF, 1) '<<<<<<<< If column F has a header, then change the 1 (lR = 1) to a 2 (lR = 2) <<<<<<<
            bFnd = False    'reset found flag
            For lC = 1 To UBc
                If vF(lR, 1) Like vDD(lC, 1) Then 'check for match
                    'this item in F exists in C, so skip to next
                    bFnd = True 'set found flag
                    Exit For
                End If
            Next lC
            If Not bFnd Then
                'item in F was checked against all items in C. No match found
                'so change this item to the changed cell (Target)
                vF(lR, 1) = vC
            End If
        Next lR
        
        'The list in F (held in vF ) has been checked and modified.
        'Now write this list back to the sheet
        rF.Value = vF
        
    End If

    
    
End Sub