Macro to go to another cell based on value of cell

[t_mo31]

Hi all,

I have a work project database and want to jump from one column to another if a certain value is entered. Specifically:

In column Y (from Y3 to infinity) if the cell contains the word "Red" or "Orange" then I want to jump to column DE (corresponding row).

I also want to repeat this macro for other columns, with them all jumping to DE - so do I need to write multiple macros, one for each column? The other columns are: AC, AH, AM, AS, AY, BE, BK, BQ, BW, CC, CI, CO, CU & DA

I'm very new at this so need help! Thanks

Tim

 

[gallen]

Hi Tim. Welcome.

This code, pasted into the code window of the sheet you wish it to work on will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sColumn As String ' column letter


    'do nothing if rows 1 or 2 or more than 1 cell has been changed.
    If Target.Row < 3 Or Target.Cells.Count > 1 Then Exit Sub
    
    'check if cell contains red or orange
    If InStr(1, UCase(Target.Value), "RED") > 0 Or InStr(1, UCase(Target.Value), "ORANGE") > 0 Then
    
        'parse the column letter
        sColumn = Replace(Cells(1, Target.Column).Address(False, False), "1", "")
        
        'if Column is any of these, select Column DE of same row
        Select Case sColumn
            Case "Y", "AC", "AH", "AM", "AS", "AY", "BE", "BK", "BQ", "BW", "CC", "CI", "CO", "CU" & "DA"
                Range("DE" & Target.Row).Select
            
        End Select
        
    End If
End Sub

 

[t_mo31]

Hi Tim. Welcome.

This code, pasted into the code window of the sheet you wish it to work on will do the trick:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sColumn As String ' column letter


    'do nothing if rows 1 or 2 or more than 1 cell has been changed.
    If Target.Row < 3 Or Target.Cells.Count > 1 Then Exit Sub
    
    'check if cell contains red or orange
    If InStr(1, UCase(Target.Value), "RED") > 0 Or InStr(1, UCase(Target.Value), "ORANGE") > 0 Then
    
        'parse the column letter
        sColumn = Replace(Cells(1, Target.Column).Address(False, False), "1", "")
        
        'if Column is any of these, select Column DE of same row
        Select Case sColumn
            Case "Y", "AC", "AH", "AM", "AS", "AY", "BE", "BK", "BQ", "BW", "CC", "CI", "CO", "CU" & "DA"
                Range("DE" & Target.Row).Select
            
        End Select
        
    End If
End Sub

Thank you so much! Works great