Change Font Color when the Cell value condition fails VBA

Question

I have created a macro to update certain values and after these values are entered they are used to create a text file for import into our system.

Below is a screen shot of the data entry screen:

enter image description here Below is the code, I have written on the worksheet:

Option Explicit
Public Rec_Cnt As Integer
Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range

Rec_Cnt = Sheets("MD").Cells(3, 7)

Set Rng1 = Range("G2:G" & Rec_Cnt + 1)
Set Rng2 = Range("M2:M" & Rec_Cnt + 1)
Set Rng3 = Range("S2:S" & Rec_Cnt + 1)
Set Rng4 = Range("D2:E" & Rec_Cnt + 1)

If Not Application.Intersect(Target, Rng1) Is Nothing Then
    If Len(Target) > 10 Then
       Call Original_Ticket_Greater_Error
    ElseIf Len(Target) < 10 Then
       Call Original_Ticket_Lesser_Error
    ElseIf Len(Target) = 10 Then
        Cells(Target.Row, 8).Value = 9
        Cells(Target.Row, 9).Value = "|"
        Cells(Target.Row, 10).Value = "|"
        Cells(Target.Row, 11).Value = "|"
        Cells(Target.Row, 12).Value = "|"
    Exit Sub
    End If
ElseIf Not Application.Intersect(Target, Rng2) Is Nothing Then
    If Len(Target) > 10 Then
       Call Original_Cnj_Ticket_Greater_Error
    ElseIf Len(Target) < 10 Then
       Call Original_Cnj_Ticket_Lesser_Error
    ElseIf Len(Target) = 10 Then
        Cells(Target.Row, 14).Value = 9
        Cells(Target.Row, 15).Value = "|"
        Cells(Target.Row, 16).Value = "|"
        Cells(Target.Row, 17).Value = "|"
        Cells(Target.Row, 18).Value = "|"
    Exit Sub
    End If
ElseIf Not Application.Intersect(Target, Rng3) Is Nothing Then
    If Len(Target) > 10 Then
       Call Original_Ticket_Greater_Error
    Exit Sub
    ElseIf Len(Target) < 10 Then
       Call Original_Ticket_Lesser_Error
    Exit Sub
    End If
ElseIf Not Application.Intersect(Target, Rng4) Is Nothing Then
    If Cells(Target.Row, 3).Value = "Sales" Or Cells(Target.Row, 3).Value = "Sales Conjunction" Then
        Cells(Target.Row, 6).Value = Application.Sum((Cells(Target.Row, 4).Value), (Cells(Target.Row, 5).Value))
        Cells(Target.Row, 6).Value = Int(Cells(Target.Row, 6).Value * 100)
    End If
End If

End Sub

Sub Original_Ticket_Greater_Error()
    MsgBox "Original Ticket Number is more than 10 characters"
End Sub

Sub Original_Cnj_Ticket_Greater_Error()
    MsgBox "Original Conj. Ticket Number is more than 10 characters"
End Sub

Sub Original_Ticket_Lesser_Error()
    MsgBox "Original Ticket Number is less than 10 characters"
End Sub

Sub Original_Cnj_Ticket_Lesser_Error()
    MsgBox "Original Conj. Ticket Number is less than 10 characters"
End Sub

Based on the code you can notice that I am updating certain cells only when the Target = 10 and otherwise not.

I wanted to change the font to RED when the Target is >10 or <10 and have tried couple of options but the font color doesn't change. I have used Target.Font.Color and similar options.

Any help is much appreciated.

Thanks, Sachin

If you create `Worksheet_Change` event handler with 1 line `Target.Font.Color = vbRed` it works, so it is unclear, why it did not work for you - maybe you should edit the question to add the code you tried. Also set `Application.EnableEvents` to false/true at start/end of your event to disable firing of the change event when you modify cells with your code. — BrakNicku, Nov 01 '18 at 18:25
Hello Ben, Tried ur option and also used some help from this page; https://stackoverflow.com/questions/14627816/excell-2007-macro-validate-data-entered-into-cell-and-show-msgbox-if-incorrect However, the macro doesn't do anything. — Sachin S, Nov 06 '18 at 13:30
[Edit](https://stackoverflow.com/posts/53107001/edit) the question to add/change code. — BrakNicku, Nov 07 '18 at 07:15