Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
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: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
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! "Stefi" wrote: 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: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
On Tue, 4 Nov 2008 19:33:01 -0800, Cynthia
wrote: 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 07150042736 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
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" wrote: On Tue, 4 Nov 2008 19:33:01 -0800, Cynthia wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
On Fri, 7 Nov 2008 00:43:00 -0800, Cynthia
wrote: 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("A1:D10") '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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
Hi Ron,
This works! Thanks. I was stuck earlier coz I specified the range incorrectly! "Ron Rosenfeld" wrote: On Fri, 7 Nov 2008 00:43:00 -0800, Cynthia wrote: 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("A1:D10") '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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting special characters in a string
On Mon, 10 Nov 2008 17:56:00 -0800, Cynthia
wrote: Hi Ron, This works! Thanks. I was stuck earlier coz I specified the range incorrectly! Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Special characters in a string variable | Excel Programming | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
String Function 'Like' and Special Characters | Excel Programming | |||
Escape for special characters in string | Excel Programming |