validation
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
|