hi
i have column containing name of the customer
i want to change pattern of each cell to 'red'
if a cell contains charters like !,@,#,$,%,^,&,*,-,_,+,= or space
how to achieve this task?
please help
--
hemu

Hi Hemant,


i have column containing name of the customer
i want to change pattern of each cell to 'red'
if a cell contains charters like !,@,#,$,%,^,&,*,-,_,+,= or space
how to achieve this task?

Try:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim sStr As String
Dim i As Long

Set Rng = Range("E1:E200") '<<=== CHANGE
Set rng2 = Intersect(Rng, Target)

If Not rng2 Is Nothing Then
For Each rCell In rng2.Cells
With rCell
For i = 1 To Len(.Value)
sStr = Mid(.Value, i, 1)
If Not IsNumeric(sStr) And Asc(sStr) < 65 Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
Next i
End With
Next rCell
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman

thanks norman
i will let u know immidiately
stay on
--
hemu


"Norman Jones" wrote:

Hi Hemant,


i have column containing name of the customer
i want to change pattern of each cell to 'red'
if a cell contains charters like !,@,#,$,%,^,&,*,-,_,+,= or space
how to achieve this task?

Try:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rng2 As Range
Dim rCell As Range
Dim sStr As String
Dim i As Long

Set Rng = Range("E1:E200") '<<=== CHANGE
Set rng2 = Intersect(Rng, Target)

If Not rng2 Is Nothing Then
For Each rCell In rng2.Cells
With rCell
For i = 1 To Len(.Value)
sStr = Mid(.Value, i, 1)
If Not IsNumeric(sStr) And Asc(sStr) < 65 Then
.Interior.ColorIndex = 3
Else
.Interior.ColorIndex = xlNone
End If
Next i
End With
Next rCell
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman

thanks
it worked
--
hemu


"Hemant_india" wrote:

hi
i have column containing name of the customer
i want to change pattern of each cell to 'red'
if a cell contains charters like !,@,#,$,%,^,&,*,-,_,+,= or space
how to achieve this task?
please help
--
hemu