Could you check my code please

[ipbr21054]

Hi,
I have this code in use
I have added the code shown in Red expecting when i leave the cell in question the Font Size Font Family etc etc to change.
Nothing happens when i leave any cells.

Can you advise / show me what ive missed please

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if more than one cell updated at a time
    If Target.Count > 1 Then Exit Sub

'   Check to see if value updated is in column B or D
    If Target.Column = 2 Or Target.Column = 4 Then
        Application.EnableEvents = False
        If UCase(Cells(Target.Row, "B")) = "REFUND" Then
            Cells(Target.Row, "D") = Abs(Cells(Target.Row, "D")) * -1
        Else
            If Cells(Target.Row, "B") = "" Then Cells(Target.Row, "D").ClearContents
        End If
        Application.EnableEvents = True
    End If
    If Not (Application.Intersect(Target, Range("A3:K28")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
        With Range("A1:L33")
        .Font.Size = 11
        .Font.Bold = True
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        Application.EnableEvents = True
        End With
    End If
End Sub

 

[RoryA]

That code will only be triggered if you actually change a cell in the range A3:K28.

 

[ipbr21054]

Hmmm
Why A3:K28 ?
Maybe i should use a separate code just for this.
Im going to start pasting a value from an email to this worksheet & once pasted its a differnet so this code will change it to match the others.

Can you advise the code i should then use please.

 

[RoryA]

Because that's what you told it to check here:

If Not (Application.Intersect(Target, Range("A3:K28")) _
      Is Nothing) Then

 

[ipbr21054]

OK i now see it.
Maybe this code should be used separatly as i did add it into the existing code where it shows the range mentioned.

can you advise

 

[RoryA]

I don't know what you are pasting where. Most likely it should be in a separate block testing if the Target intersects whatever range you expect to paste into.

 

[ipbr21054]

Im going to copy numbers like this from my email 19-05557-94326 as paste to my worksheet

The Range is A1:L33
Once pasted its a different font family etc etc.
So the code will change it to then match the other values on the sheet.

       .Font.Size = 11
        .Font.Bold = True
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter

 

[ipbr21054]

Well im struggling with this.

I have this working to a point.
Ive put it on a button at present as i didnt know how to write it for when i just leave the cell & had to start to move on with it.

One thing i cant sort out is shown in the screen of which is the whole purpose of this code.
The cell in this example E15 shows after the code has run the font etc didnt change ???

Private Sub formatbutton_Click()
With ActiveSheet.Range("A1:L33")
        .Font.Size = 11
        .Font.Bold = True
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
 End With
End Sub

 

[RoryA]

In what way did it not change? I can't tell much from that picture.

 

[ipbr21054]

Look at the number its supposed to matched all the above

Like its very faint in colour