Advice for adding line of code to existing code please

[ipbr21054]

Hi,

I am using the code below which works fine.

Private Sub Worksheet_Change(ByVal Target As Range)  Dim c As Range
  On Error GoTo AllowEvents
  If Target.Count > 1000 Then Exit Sub
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Target
      If c.Row > 5 And c.Column = 2 Then
          If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
          Application.EnableEvents = False
          MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
          c.Value = ""
          c.Select
        End If
      End If
    Next
  End If
AllowEvents:
  Application.EnableEvents = True


End Sub

What i am having an issue with now is trying to add something like the below into the code above.

Range("B").Characters(Start:=10, Length:=1).Font.ColorIndex = 3

The code needs to work for any cell in column B so when a value is typed into the cell the 10th character will be shown in a Red font colour.

 

[Akuini]

Maybe this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
  On Error GoTo AllowEvents
  If Target.Count > 1000 Then Exit Sub
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each c In Target
      If c.Row > 5 And c.Column = 2 Then
          If Len(c.Value) <> 17 And Len(c.Value) > 0 Then
          Application.EnableEvents = False
          MsgBox "VIN MUST BE 17 CHARACTERS", vbCritical, "VIN CHARACTER COUNT MESSAGE"
          c.Value = ""
          c.Select
          [COLOR=#0000ff]Else
                c.Characters(Start:=10, Length:=1).Font.ColorIndex = 3[/COLOR]
                
         End If
      End If
    Next
  End If
AllowEvents:
  Application.EnableEvents = True


End Sub

 

[ipbr21054]

Thanks but that made no difference

 

[mohadin]

Thanks but that made no difference

It Dose work perfectly

 

[ipbr21054]

Hi,
Strange.

Here is my worksheet if maybe you could see the difference.

http://www.mediafire.com/file/mhevof7jm77xa5d/TEST2.xlsm/file

 

[Peter_SSs]

Here is my worksheet if maybe you could see the difference.

Yes, that Worksheet_Change event code does not include the two lines of code Akuini suggested for you.

 

[ipbr21054]

Please check link again as first link was incorrect

 

[Peter_SSs]

Please check link again as first link was incorrect

Works for me. If I enter 17 characters in column B, or even just confirm an existing value (F2 -> Enter) then the 10th character turns red.

 

[ipbr21054]

Please see my excel sheet TEST2

 

[Akuini]

Worksheet_Change event runs if you change a cell content, if your data is already there and then you add the code then it won’t run.
Just select the data in column B then hit ctrl+C (copy) , the font will turn red.