VBA Mismatch Error Help

[rmd45021]

I'm having trouble with copying and pasting a row of data from one sheet to another, if I try and paste I get a mismatch error. Also if I try and delete more then one cell at a time, I get the mismatch error. Heres the code I am running...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target <> "" Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
If Target.Column = 6 And Target <> "" Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
If Target.Column = 8 And Target <> "" Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
If Target.Column = 7 And Target <> "" Then
Target.Value = UCase(Target.Value)
End If
End Sub

Please let me know if there is anything I can do to fix this problem. Any help is EXTREAMLY appreciated.

-RYAN

[tigeravatar]

rmd45021,

Welcome to the forum! In the future, please surround your code in code tags. See forum rules (link in my sig) for how.

As to your question, give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngChg As Range: Set rngChg = Intersect(Union(Columns("B"), Columns("F:H")), Target)
    If rngChg Is Nothing Then Exit Sub
    
    Dim ChgCell As Range
    Application.EnableEvents = False
        
        For Each ChgCell In rngChg
            If Trim(ChgCell.Value) <> vbNullString Then
                Select Case ChgCell.Column
                    Case 2, 6, 8: ChgCell.Value = StrConv(ChgCell.Value, vbProperCase)
                    Case 7:       ChgCell.Value = UCase(ChgCell.Value)
                End Select
            End If
        Next ChgCell
    
    Application.EnableEvents = True
    
End Sub

[rmd45021]

My apologies for not reading the forum rules. I'll go through them as soon as I'm done typing. Tiger....THANK YOU SOOOO MUCH!!!! Works great!