Advice for adding line of code to existing code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Hi,

I am using the code below which works fine.

Code:
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.

Code:
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Maybe this:
Code:
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,322
Please check link again as first link was incorrect
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,646
Office Version
365
Platform
Windows
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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,122
Messages
5,466,819
Members
406,500
Latest member
Tknotmaxx

This Week's Hot Topics

Top