excel vba program - check values in cell for ALL CAPs or uppercase return Boolean value

Hello,

I am writing a vba program that loops through all the rows in an excel spreadsheet.  For each row, I need it to check a particular cell and highlight the row in a light blue when it finds a cell that contains a value with all capitals or UPPER CASE.  

I need a check to return T or F to tell the program whether or not ALL values in the cell are in CAPs.

Is there a vba function I can use to accomplish this?   Thanks
lchaAsked:
Who is Participating?
 
Patrick MatthewsCommented:
If you mean "on each row, check a particular column to see if it is all caps"...


Sub LookAtCells()

    Dim LastR As Long, Counter As Long

    LastR = Cells(Rows.Count, "a").End(xlUp).Row
    For Counter = 1 To LastR
        If StrComp(Cells(Counter, "a"), UCase(Cells(Counter, "a")), vbBinaryCompare) = 0 Then
            'code for all caps
        Else
            'code for not all caps
        End If
    Next

End Sub

Open in new window

0
 
carsRSTCommented:
'cell to check
col = 1

For i = 1 To 65536  'is using Excel 2003
    If Cells(i, col).Value = UCase(Cells(i, col).Value) And Cells(i, col) <> "" Then

        Rows(i & ":" & i).Interior.ColorIndex = 37

    End If
Next i
0
 
Patrick MatthewsCommented:
carsRST,

Please note that your suggestion will not work if there is a module level declaration for Option Compare Text.  That is why I used the StrComp function, because it allows me to specify the text comparison mode.

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cyberkiwiCommented:
Just speeding up cars' code - it only checks the rows in use not 60,000+ or more in Excel 2007

'column to check
col = 1

For Each c in Intersect(UsedRange.Rows, Columns(col))
    If c.Value <> "" AND c.Value = UCase(C.Value) Then
        c..Interior.ColorIndex = 37
    End If
Next i

Patrick, UCase converts it to uppercase and checks that to pre-convert
0
 
Patrick MatthewsCommented:
cyberkiwi,

>>Patrick, UCase converts it to uppercase and checks that to pre-convert

With respect, please read what I wrote:

"Please note that [carsRST's] suggestion will not work if there is a module level declaration for Option Compare Text [emphasis added]"

Try creating a module, and putting this code in it:


'begin module
Option Explicit
Option Compare Text

Sub Test1()

    MsgBox "word" = UCase("word")

End Sub
' end module


If you run that sub, the MsgBox will say "True": by using Option Compare Text, we have told VBA to ignore case in string comparisons.

Now, add another module, and put in this code:


'begin module
Option Explicit

Sub Test2()

    MsgBox "word" = UCase("word")

End Sub
' end module


When you run *that* code, the MsgBox will say "False", as unless we put Option Compare Text (or Option Compare Database for Access) in the module declarations, VBA is case-sensitive in string comparisons.


Not knowing whether or not Option Compare Text might be used, I maintain that the safest couse is to use StrComp, which allows you to specify the string comparison mode regardless of what is specified in the module declarations.

Patrick
0
 
lchaAuthor Commented:
thanks everyone and apology for the delay on this.  I ended up using matthewpatricks solution.  thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.