Unable to find line of code that enters value in cell D21

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
Afternoon,
I am unable to find what triggers the code to place the YEAR in cell D21

My sheet works like this.
A value is placed into cell A21
I then make a selctiion from a drop down in cell B21
I then type the customers name in cell D21

As soon as i click in cell C21 the date is then shown in cell D21

1, I am unable to find what or where in the code makes this happen, Am i looking in the correct code ?
1, I would like change the trigger option from C21 to A21

Once this code is found my page will then work like this.

A value is placed into cell A21
Then the YEAR should be placed in cell D21
Then my userform will open

Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "G"

'   *** Specify start row ***
    myStartRow = 21
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True

Application.EnableEvents = False
Select Case Mid(Range("A21").Value, 10, 1)
Case Is = "S"
        Range("D21").Value = "1995"
Case Is = "T"
        Range("D21").Value = "1996"
Case Is = "V"
        Range("D21").Value = "1997"
Case Is = "W"
        Range("D21").Value = "1998"
Case Is = "X"
        Range("D21").Value = "1999"
Case Is = "Y"
        Range("D21").Value = "2000"
Case Is = "1"
        Range("D21").Value = "2001"
Case Is = "2"
        Range("D21").Value = "2002"
Case Is = "3"
        Range("D21").Value = "2003"
Case Is = "4"
        Range("D21").Value = "2004"
Case Is = "5"
        Range("D21").Value = "2005"
Case Is = "6"
        Range("D21").Value = "2006"
Case Is = "7"
        Range("D21").Value = "2007"
Case Is = "8"
        Range("D21").Value = "2008"
Case Is = "9"
        Range("D21").Value = "2009"
Case Is = "A"
        Range("D21").Value = "2010"
Case Is = "B"
        Range("D21").Value = "2011"
Case Is = "C"
        Range("D21").Value = "2012"
Case Is = "D"
        Range("D21").Value = "2013"
Case Is = "E"
        Range("D21").Value = "2014"
Case Is = "F"
        Range("D21").Value = "2015"
Case Is = "G"
        Range("D21").Value = "2016"
Case Is = "H"
        Range("D21").Value = "2017"
Case Is = "J"
        Range("D21").Value = "2018"
Case Is = "K"
        Range("D21").Value = "2019"
End Select

Application.EnableEvents = True

End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The section starting with:

Rich (BB code):
Select Case Mid(Range("A21").Value, 10, 1)
Case Is = "S"
        Range("D21").Value = "1995"

is where that cell gets the year.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
I agree with that BUT at what point does it get entered into cell D21 when i click in cell C21
I am unable to dind anything so when cell C21 is selected enter the YEAR in cell D21 this is whats making me think
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If you select a cell anywhere in columns A:G from row 21 down to the last entered row, your code will fire.
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
I never spotted that.

Many thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top