Merging two worksheet change codes,now confused

[ipbr21054]

Hi,

I have two codes which both need to be under the Private Sub Worksheet_Change(ByVal Target As Range)

Like normal ive got into a mess & now confused.
Could you advise where ive gone wrong please.

Thanks.
Merged code supplied below

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$A$6" Then
Application.EnableEvents = False
Dim CustomerName As String
Worksheets("DATABASE").Select
CustomerName = Range("A6")
Worksheets("INFO").Select
Worksheets("INFO").Range("CF2").Select
If Worksheets("INFO").Range("CF2").Offset(1, 0) <> "" Then
Worksheets("INFO").Range("CF2").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Interior.ColorIndex = 6
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.VerticalAlignment = xlBottom
ActiveCell.VerticalAlignment = xlCenter
Selection.Borders.LineStyle = xlContinuous
ActiveCell.RowHeight = 19.5
ActiveCell.Font.Bold = True
Worksheets("DATABASE").Select
Application.EnableEvents = True
Else
With Target
        If .Column = 13 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
            End If
End With
End Sub

 

[JLGWhiz]

What is the name of the worksheet that hosts the code?

 

[ipbr21054]

DATABASE

I merged the two codes incorrectly.
When i leave cell A6 the text should be forced to upper case & the code run.

 

[JLGWhiz]

See if this is what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$6" Then
    Dim CustomerName As String
    CustomerName = Target.Value
    With Worksheets("INFO").Range("CF2").Cells(Rows.Count, "CF").End(xlUp)(2)
        .Value = CustomerName
        .Interior.ColorIndex = 6
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .VerticalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous
        .RowHeight = 19.5
        .Font.Bold = True
    End With
Else
    With Target
            If .Column = 13 Then
                Application.EnableEvents = True
                Exit Sub
            End If
            If .Count = 1 And Not .HasFormula Then
                    .Value = UCase(.Value)
            End If
    End With
End If
Application.EnableEvents = True
End Sub

 

[ipbr21054]

That pops up a run time error 1004
For the 6th line of code.

If you can merge these two below problem solved.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$6" Then
Application.EnableEvents = False
Dim CustomerName As String
Worksheets("DATABASE").Select
CustomerName = Range("A6")
Worksheets("INFO").Select
Worksheets("INFO").Range("CF2").Select
If Worksheets("INFO").Range("CF2").Offset(1, 0) <> "" Then
Worksheets("INFO").Range("CF2").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Interior.ColorIndex = 6
ActiveCell.HorizontalAlignment = xlCenter
ActiveCell.VerticalAlignment = xlBottom
ActiveCell.VerticalAlignment = xlCenter
Selection.Borders.LineStyle = xlContinuous
ActiveCell.RowHeight = 19.5
ActiveCell.Font.Bold = True
Worksheets("DATABASE").Select
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 13 Then Exit Sub
        If .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase(.Value)
            Application.EnableEvents = True
        End If
    End With
End Sub

Thanks

 

[JackDanIce]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r   As Range
    
    Application.EnableEvents = False
    
        If Target.Address = "$A$6" Then
            With sheets("INFO").Range("CF2")
                If Len(.Offset(1).Value) Then
                    Set r = .End(xlDown).Offset(1)
                    With .End(xlDown).Offset(1)
                        .Value = ActiveSheet.Cells(6, 1).Value
                        .Interior.ColorIndex = 6
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlBottom
                        .VerticalAlignment = xlCenter
                        .Borders.LineStyle = xlContinuous
                        .RowHeight = 19.5
                        .Font.Bold = True
                    End With
                End If
             End With
        End If
        
    With Target
        If .Column <> 13 And .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase$(.Value)
            Application.EnableEvents = True
        End If
    End With
        
    Application.EnableEvents = True
    
End Sub

 

[ipbr21054]

Great,works a treat.

Now i dont see all the flashing pages just a quick copy / paste.


One thing i did notice is when the value from cell A6 is pasted to the INFO sheet its small case & would like it to be upper case.

So currently cell A6 reads tom jones,when you leave that cell it changes to TOM JONES which is correct.
I then go to the INFO sheet expecting it to also be TOM JONES but its tom jones.

Could you advise please.
Many thansk

 

[JLGWhiz]

That pops up a run time error 1004
For the 6th line of code.

That line should read

With Worksheets("INFO").Cells(Rows.Count, "CF").End(xlUp)(2)

Sorry about that.

 

[JackDanIce]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r   As Range
    
    Application.EnableEvents = False
    
        If Target.Address = "$A$6" Then
            With sheets("INFO").Range("CF2")
                If Len(.Offset(1).Value) Then
                    Set r = .End(xlDown).Offset(1)
                    With .End(xlDown).Offset(1)
                        .Value = UCase$(ActiveSheet.Cells(6, 1).Value)
                        .Interior.ColorIndex = 6
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlBottom
                        .VerticalAlignment = xlCenter
                        .Borders.LineStyle = xlContinuous
                        .RowHeight = 19.5
                        .Font.Bold = True
                    End With
                End If
             End With
        End If
        
    With Target
        If .Column <> 13 And .Count = 1 And Not .HasFormula Then
            Application.EnableEvents = False
            .Value = UCase$(.Value)
            Application.EnableEvents = True
        End If
    End With
        
    Application.EnableEvents = True
    
End Sub

The flashing pages are due to using .Select to activate sheets. It's just a property of the sheet object and usually not needed
i.e.

Sheets("Sheet1").Range("A1").Value = 1

And

Sheets("Sheet1").Select
Range("A1").Value = 1

Will both put 1 into cell A1 of Sheet1 but the second version makes Sheet1 visible for the users benefit, has no impact on the value of the cell or its contents.

 

[ipbr21054]

Thanks,
The code in the last post now transfers it in uppercase.

My last goal for tonight is once the code has been pasted to the INFO sheet it needs to be sorted A-Z
Cell range is CF2 then down the page

Thanks