Hi all,

I spent some time in another solution for looking account number from
worksheet, but no progress at all :(

In the first column of worksheet, there are some cells which are mad
up of names and an unique account number (6-digit-form).

However, some cells are padded with spaces, i.e.
123456 ABC
123457 CDE
345678 EFG

I would like to find all cells with account number out and highligh
them.

My code:
Sub find_Acct()
Dim result As Integer, c As Range

For Each c In ActiveSheet.Columns(1)
If IsNumeric(Left(Trim(c.Value), 6)) Then
'Highlight the cell
End If
Next c
End Sub

Any help is appreciated

--
Message posted from http://www.ExcelForum.com

Hi
try
For Each c In ActiveSheet.Columns(1)
If IsNumeric(Left(Trim(c.Value), 6)) and trim(c.value)<"" Then
c.interior.colorindex=3
End If
Next c
End Sub

But you can achieve the same without VBA (using conditional format).
e.g. try the following
- select cells A1:A100
- goto 'Format - Conditional Format'
- enter the following formula
=(ISNUMBER(--LEFT(TRIM(A1),6))*(TRIM(A1)<"")
- choose a format for these cells


--
Regards
Frank Kabel
Frankfurt, Germany


Hi all,

I spent some time in another solution for looking account number from
a worksheet, but no progress at all :(

In the first column of worksheet, there are some cells which are made
up of names and an unique account number (6-digit-form).

However, some cells are padded with spaces, i.e.
123456 ABC
123457 CDE
345678 EFG

I would like to find all cells with account number out and highlight
them.

My code:
Sub find_Acct()
Dim result As Integer, c As Range

For Each c In ActiveSheet.Columns(1)
If IsNumeric(Left(Trim(c.Value), 6)) Then
'Highlight the cell
End If
Next c
End Sub

Any help is appreciated.


---
Message posted from http://www.ExcelForum.com/

Thanks.

But I cannot get rid of the same problem -- Error 13: Type Mismatch, s
I do not know how to fix it.

Warning on this line:
If Trim(c.Value) < "" And IsNumeric(Left(Trim(c.Value), 6)) Then

Thanks

--
Message posted from http://www.ExcelForum.com

Hi
try
For Each c In ActiveSheet.Columns(1)
if len(trim(c.value))=6 then
If IsNumeric(Left(Trim(c.Value), 6)) Then
c.interior.colorindex=3
end if
End If
Next c
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


Thanks.

But I cannot get rid of the same problem -- Error 13: Type Mismatch,
so I do not know how to fix it.

Warning on this line:
If Trim(c.Value) < "" And IsNumeric(Left(Trim(c.Value), 6)) Then

Thanks.


---
Message posted from http://www.ExcelForum.com/