Could you check my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,738
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
That code will only be triggered if you actually change a cell in the range A3:K28.
 
Upvote 0
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.
 
Upvote 0
Because that's what you told it to check here:

Code:
If Not (Application.Intersect(Target, Range("A3:K28")) _
      Is Nothing) Then
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.

VBA Code:
       .Font.Size = 11
        .Font.Bold = True
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
 
Upvote 0
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 ???


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

Attachments

  • 185.jpg
    185.jpg
    151.7 KB · Views: 9
Upvote 0
In what way did it not change? I can't tell much from that picture.
 
Upvote 0
Look at the number its supposed to matched all the above

Like its very faint in colour
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top