Highlighting special characters in a string

[Cynthia]

Hi everybody,

I've got an issue that I am racking my brains out but am no closer to a
solution. Appreciate if anyone can help.

Currently I have to import a .txt file into excel as text.
Sometimes my source file contains funny characters instead of proper digits
such as "²", "±", "´" which are not consistent.
I'll like to be able run a macro that highlights the row that contains any
characters which are not proper digits.

E.g. in my column d, i get 0715±0042736 in a certain row, I want to
highlight it.

Got error when I tried this but cant think of a workaround:
Sub Color()
Dim R As Range
Dim V As Variant
Dim S As String
S = "1,2,3,4,5,6,7,8,9,0" '<<<< This is the data to test against
V = Split(S, ",")
For Each R In Range("d1:d500") '<<<< This is the range with data

If InStr(R, V) Then
cell.EntireRow.Interior.ColorIndex = 3
Else
cell.EntireRow.Interior.ColorIndex = 0
End If
Next R
End Sub


Thanks a lot for any assistance!

 

[Stefi]

I think you meant this:
Sub Color()
Dim R As Range
Dim S As String
S = "1,2,3,4,5,6,7,8,9,0" '<<<< This is the data to test against
For Each R In Range("d1:d500") '<<<< This is the range with data
For charpos = 1 To Len(R)
If InStr(S, Mid(R, charpos, 1)) = 0 Then
R.EntireRow.Interior.ColorIndex = 3
Exit For
Else
R.EntireRow.Interior.ColorIndex = 0
End If
Next charpos
Next R
End Sub

But there is a simpler solution:

Sub Color2()
Dim R As Range
For Each R In Range("d1:d500") '<<<< This is the range with data
If Not WorksheetFunction.IsNumber(R) Then
R.EntireRow.Interior.ColorIndex = 3
Else
R.EntireRow.Interior.ColorIndex = 0
End If
Next R
End Sub

And you can do it without any VBA, via Conditional formatting:

Select rows 1:500 and apply CF with this formula:

=NOT(ISNUMBER($D1))
and choose a pattern!

Regards,
Stefi


„Cynthia†ezt írta:

 

[Cynthia]

Hi Stefi,

Your first suggestion works. I'm afraid the other 2 didnt manage to work for
me.
Just a query. If I need to expand the check area, ie. check column A to D
from Range 1 to 500, how should I modify the code?

I tried changing the line For Each R In Range("d1:d500") ' to For Each R In
Range("a1:d500") ', it didnt seem to work.

Thanks!

 

[Ron Rosenfeld]

Hi everybody,

I've got an issue that I am racking my brains out but am no closer to a
solution. Appreciate if anyone can help.

Currently I have to import a .txt file into excel as text.
Sometimes my source file contains funny characters instead of proper digits
such as "²", "±", "´" which are not consistent.
I'll like to be able run a macro that highlights the row that contains any
characters which are not proper digits.

E.g. in my column d, i get 0715±0042736 in a certain row, I want to
highlight it.

Got error when I tried this but cant think of a workaround:
Sub Color()
Dim R As Range
Dim V As Variant
Dim S As String
S = "1,2,3,4,5,6,7,8,9,0" '<<<< This is the data to test against
V = Split(S, ",")
For Each R In Range("d1:d500") '<<<< This is the range with data

If InStr(R, V) Then
cell.EntireRow.Interior.ColorIndex = 3
Else
cell.EntireRow.Interior.ColorIndex = 0
End If
Next R
End Sub


Thanks a lot for any assistance!

Try this:

=========================
Option Explicit
Sub NonDigits()
Dim rg As Range, c As Range
Set rg = Range("A1:a100") 'Range to be checked
For Each c In rg
If Not c.Text Like Application. _
WorksheetFunction.Rept("#", Len(c.Text)) Then
c.EntireRow.Interior.Color = vbRed
Else
c.EntireRow.Interior.Color = xlNone
End If
Next c
End Sub
===========================

The above will highlight any row that does not contain all digits in the range
being checked.
--ron

 

[Cynthia]

Hi Ron,

Thanks for your help but I am still not getting all the rows with special
characters in column A to D hightlighted automatically.
I still have to go in and change the column names individually.

Regards
Cynthia

 

[Ron Rosenfeld]

Hi Ron,

Thanks for your help but I am still not getting all the rows with special
characters in column A to D hightlighted automatically.
I still have to go in and change the column names individually.

Regards
Cynthia

That has to do with how you step through the range. As written, if you specify
a multi-column range, all the cells in any particular row would have to fail
the test.

To check for just a single cell failing the test, do something like:


==============================
Option Explicit
Sub NonDigits()
Dim rg As Range
Dim rw As Long, col As Long
Set rg = Range("A110") 'Range to be checked

For rw = 1 To rg.Rows.Count
rg(rw, 1).EntireRow.Interior.Color = xlNone
For col = 1 To rg.Columns.Count
With rg(rw, col)
If Not .Text Like Application. _
WorksheetFunction.Rept("#", Len(.Text)) Then
.EntireRow.Interior.Color = vbRed
End If
End With
Next col
Next rw
End Sub
===============================
--ron

 

[Cynthia]

Hi Ron,

This works! Thanks. I was stuck earlier coz I specified the range incorrectly!

 

[Ron Rosenfeld]

Hi Ron,

This works! Thanks. I was stuck earlier coz I specified the range incorrectly!

Glad to help. Thanks for the feedback.
--ron