Run-Time Error

[Guest]

The following macro gives me a "run-time error '13' Type mismatch" pop-up
when I delete row. When I click on Debug it is the ".Value=UCase(.Value)"
that's highlighted. What is wrong? Connie

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("B18:B100,F18:F100")) Is
Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

 

[Dave Peterson]

Since you're deleting a row, you're changing the cell in column B and F. Two
cells don't have a simple value you can check.

I think I'd look for multiple cells and quit if I find them:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.cells.count > 1 then exit sub
If Not (Application.Intersect(Target, Range("B18:B100,F18:F100")) Is Nothing) _
Then
With Target
If Not .HasFormula Then
application.enableevents = false
.Value = UCase(.Value)
application.enableevents = true
End If
End With
End If
End Sub

The .enableevents stop the change from firing the code again.

 

[Guest]

Thank you very much. That works very nicely. Connie