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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.