test first character in each cell to see if alpha

[wbsmith]

I want to be able to go through a range (all in col A) and if the first non-blank character is a Letter, change the font and cell color.

In excel I can use "Trim" to clear leading blanks, then use "code" to test the ascii value of the first character.

in VBA I can use the trim and assign cell valuable to a variable, but don't know how to pick off first character in string and test to see if it is an alpha (versus a blank, or a number)

thanks

[norie]

The VBA equivalent of CODE is Asc.

And you can use the Left function like you can on a worksheet.

[Bill Rockenbach]

Hi wbsmith,

THis code will change the cell color to res if the cell is not empty and the first character of the cell value is not numeric.

Sub FindFirstAlpa() 
     
    Dim c As Range 
     
    For Each c In Range("A1", Range("A65536").End(xlUp)) 
        If Not IsNumeric(Left(c, 1)) And Not c = Empty Then 
            c.Interior.ColorIndex = 3 
        End If 
    Next c 

hope this helps

Bill


End Sub

[firefytr]

You can also do this with native conditional formatting...

1) Select the entire column A (click the A column header)
2) Format --> Conditional Formatting --> Formula Is ..
3) ..

=(Not(ISBLANK(A1)))*((CODE(UPPER(LEFT(TRIM(A1),1)))>64)*(CODE(UPPER(LEFT(TRIM(A1),1)))<91)) 

4)Format as desired.


HTH

[wbsmith]

all, great info, thanks. quick general questions
1) I've read much of vba book by walkenback. Great stuff, but jumps around. Is there a programming reference that presents vba programming in a more comprehensive fashion? There are so many functions, objects, ... that I don't feel comfortable I know how they really function

2) Mental glitch: what does the "Set" command do and when do you use it instead of an assignment "=" operator

3) In conditional formatting using formula, how does it know what row to evaluate...that is, to test A1 for row 1, A2 for row 2? what if you wanted A1 to test A2?

Help here has been invaluable. I was CS guy in college, but years ago now. Lots of fun to be able to play, but only because of the excellent help I can get when I am stuck. tried the cond formatting...am going to try the macro route too.