Insert new row & apply Ucase

[ipbr21054]

Hi,

Can you advise please the correct way to apply Ucase for when i insert a new row each time at A6:H6

I have this code below but keep getting Run Time Error 13

Private Sub InsertNewRow_Click()Rows("6:6").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6").Select
Range("A6:H6").Font.Size = 18
Range("A4:H6").Font.Bold = True
Range("A6:H6").Interior.ColorIndex = 6
Range("A6:H6").Borders.LineStyle = xlContinuous
Range("A6:H6").Borders.Weight = xlThin
Range("A6:H6").HorizontalAlignment = xlCenter
Range("A6:H6").VerticalAlignment = xlCenter
Range("A6:H6").Name = "Calibri"
Range("A6:H6").RowHeight = 30
[COLOR=#ff0000]Range("A6:H6").Value = UCase(Range("A6:H6").Value)[/COLOR]
End Sub

Many Thanks

 

[ipbr21054]

@trunten
I see now it has.
I see the ucase work & the 10th character change to red.

Can you look at the file you have and see why the year isnt then put in cell E7 and the character has changed red ?

Thanks

 

[trunten]

Hi @ipbr21054

Have a look at this:

https://www.dropbox.com/s/zftk15fcs062s8z/mrexcel_TEST3.xlsm?dl=1

I've added a new sheet for you which contains a lookup for the VIN years. In my opinion this would make it easier for you to update in the future. You will not have to change any vba code, simply add the new values to the bottom of the list as and when required.

 

[ipbr21054]

Thanks for the effort & time to do this.
Im going to leave the sheet as it is where the 10th character changes red & the date then put into cell E7

 

[trunten]

But that sheet I provided literally does everything you asked for! Oh well as long as you’re happy