Hi All

I have prepared the code for the worksheet as follows. This code converts
the text to upper case format from any cell of the worksheet. Also it checks
for the specific text as well. This code works fine for all columns and rows
in the worksheet.
But I want to apply this code to column B only instead of whole worksheet.
Means whatever I will type in Column B should be changed to Uppercase and
also check for particular text, for example QUARTZ and SODA LIME.

Any help would be greatly appreciated.
Kind Regards
Harish


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula = True Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then

Else
MsgBox "Check the Spell"
Target.Select
End If
Application.EnableEvents = True

End Sub

if intersect(target, me.range("B:B")) is nothing then
exit sub
end if

(after the .cells.count check)

Harish wrote:

Hi All

I have prepared the code for the worksheet as follows. This code converts
the text to upper case format from any cell of the worksheet. Also it checks
for the specific text as well. This code works fine for all columns and rows
in the worksheet.
But I want to apply this code to column B only instead of whole worksheet.
Means whatever I will type in Column B should be changed to Uppercase and
also check for particular text, for example QUARTZ and SODA LIME.

Any help would be greatly appreciated.
Kind Regards
Harish

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula = True Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then

Else
MsgBox "Check the Spell"
Target.Select
End If
Application.EnableEvents = True

End Sub

--

Dave Peterson

Add this bit of code:

If Target.Column < 2 Then
Exit Sub
End If

RBS

"Harish" wrote in message
...
Hi All

I have prepared the code for the worksheet as follows. This code converts
the text to upper case format from any cell of the worksheet. Also it
checks
for the specific text as well. This code works fine for all columns and
rows
in the worksheet.
But I want to apply this code to column B only instead of whole worksheet.
Means whatever I will type in Column B should be changed to Uppercase and
also check for particular text, for example QUARTZ and SODA LIME.

Any help would be greatly appreciated.
Kind Regards
Harish


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula = True Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then

Else
MsgBox "Check the Spell"
Target.Select
End If
Application.EnableEvents = True

End Sub

Thanks for your help.That works fine now.

Regards
Harish



"RB Smissaert" wrote:

Add this bit of code:

If Target.Column < 2 Then
Exit Sub
End If

RBS

"Harish" wrote in message
...
Hi All

I have prepared the code for the worksheet as follows. This code converts
the text to upper case format from any cell of the worksheet. Also it
checks
for the specific text as well. This code works fine for all columns and
rows
in the worksheet.
But I want to apply this code to column B only instead of whole worksheet.
Means whatever I will type in Column B should be changed to Uppercase and
also check for particular text, for example QUARTZ and SODA LIME.

Any help would be greatly appreciated.
Kind Regards
Harish


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.HasFormula = True Then
Exit Sub
End If
If Target.Cells.Count 1 Then
Exit Sub
End If
Application.EnableEvents = False
Target.Value = UCase(Target.Text)
If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then

Else
MsgBox "Check the Spell"
Target.Select
End If
Application.EnableEvents = True

End Sub