Please can you advise on how to simplify this code

[ipbr21054]

Hi,

As per title.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim cel As Range
Dim ns As Variant
Set ws = Sheets("MC LIST")
For Each cel In ws.Range("B8", ws.Range("B" & ws.Rows.Count).End(xlUp))
If Len(cel) > 9 Then cel.Characters(Start:=10, Length:=1).Font.ColorIndex = 3
Next cel

    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False

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

    Application.EnableEvents = True


End Sub

 

[ipbr21054]

Thanks to you all,
I will look at these and see which to use.

Have a nice day

 

[jasonb75]

I will look at these and see which to use.

Ultimately that is your choice, we might be able to offer better advice if you told us what you were trying to do, you asked us how you could simplify the code, but it was already simple.

@Yongle and @GWteB have used some alternative methods to make the code more compact.
In my first reply, I kept your original (simple) code, doing nothing more than removing a few lines that appeared to be unnecessary.
In post 9, I've shortened the code from my first reply by using @GWteB's idea to replace the case selection, it is a great way to reduce the amount of code, but it is only simplifying the code if you understand it so that you can change it when you need to.