Quote:
Originally Posted by jinga
Hi all,
brand new member of the site with a bit of a puzzler. Not sure where to start or indeed if this is the place to ask!!
In a nutshell I have a spreadsheet of data that i need to go through to find data that meets a specific criteria and I am not sure where to start.
Best example i can give is a telephone number.
Rather than search the spreadsheet for a specific number i want to be able to get Excel to show me all instances of an eleven digit number regardless of what the actual number is.
Hopefully that makes sense?
Cheers
D.
|
play with this vba code. changes all the mobile number contained cells background to red color
__________________________________________________ ___________
' created and edited by Bala Sesharao
Sub find_mobile()
Dim x_cordinate, y_cordinate As Variant
Dim resultant As String
x_cordinate = 1
y_cordinate = 1
Sheets("sheet1").Cells(1, 1).Select
For x_cordinate = 1 To 65000
For y_cordinate = 1 To 256
resultant = Sheets("sheet1").Cells(x_cordinate, y_cordinate)
If Len(resultant) = 11 And IsNumeric(resultant) Then
Sheets("sheet1").Cells(x_cordinate, y_cordinate).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next y_cordinate
Next x_cordinate
End Sub
Private Sub Workbook_Open()
Call find_mobile
End Sub
____________________________________