Montu,

Try it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1,G1")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
If Application.WorksheetFunction.IsText(Target.Value) Then
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"Montu" wrote in message
...
I have designed my worksheet by using format "Merge Cell" like
A B C D E F G H I
J K
1 PAN NO. ABCDEF1234G TAN NO. VWXY012345Z
From B1 to E1 & G1 to K1 is formated cell "Merge". I want whenever write in
B1 or G1 it will be automatically Capital Letter. For this purpose I asked
for help & got a VBA code from this forum & also have used to my worksheet
(by pressing Alt+f11). The code is as follows -
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("b1:E1")
r = Range("g1:k1")
Application.EnableEvents = False
If Application.WorksheetFunction.IsText(r) Then
r.Value = UCase(r.Value)
End If
Application.EnableEvents = True
End Sub
But it's not working means it's not being automatic capital letter. Help me
what should I do.