How can I search for words in all caps in Excel?

As part of a macro, I'd like to be able to identify and re-format only cells
containing words in all caps. Is that doable?

Thanks!

Select your data and run this macro:

Sub OnlyUpper()
Dim cell As Range
For Each cell In Selection
If cell.Value = UCase(cell.Value) Then
cell.Font.ColorIndex = 3 'make font color = red
End If
Next
End Sub

---
HTH
Jason
Atlanta, GA


"Nexan" wrote:

> As part of a macro, I'd like to be able to identify and re-format only cells
> containing words in all caps. Is that doable?
>
> Thanks!

Okay, I've hit a snag with this. I just realized that this script only seems
to be finding cells containing nothing but letters in all caps. Is there a
way to find cells containing some words in all caps and some in
upper/lowercase?

"Jason Morin" wrote:

> Select your data and run this macro:
>
> Sub OnlyUpper()
> Dim cell As Range
> For Each cell In Selection
> If cell.Value = UCase(cell.Value) Then
> cell.Font.ColorIndex = 3 'make font color = red
> End If
> Next
> End Sub
>
> ---
> HTH
> Jason
> Atlanta, GA
>
>
> "Nexan" wrote:
>
> > As part of a macro, I'd like to be able to identify and re-format only cells
> > containing words in all caps. Is that doable?
> >
> > Thanks!

You could use LCASE or the Like operator.

Nick


"Nexan" <[email protected]> wrote in message
news:[email protected]...
> Okay, I've hit a snag with this. I just realized that this script only
> seems
> to be finding cells containing nothing but letters in all caps. Is there a
> way to find cells containing some words in all caps and some in
> upper/lowercase?
>
> "Jason Morin" wrote:
>
>> Select your data and run this macro:
>>
>> Sub OnlyUpper()
>> Dim cell As Range
>> For Each cell In Selection
>> If cell.Value = UCase(cell.Value) Then
>> cell.Font.ColorIndex = 3 'make font color = red
>> End If
>> Next
>> End Sub
>>
>> ---
>> HTH
>> Jason
>> Atlanta, GA
>>
>>
>> "Nexan" wrote:
>>
>> > As part of a macro, I'd like to be able to identify and re-format only
>> > cells
>> > containing words in all caps. Is that doable?
>> >
>> > Thanks!

Could you possibly give me an example of that?

Thanks!

"Nick" wrote:

> You could use LCASE or the Like operator.
>
> Nick
>
>
> "Nexan" <[email protected]> wrote in message
> news:[email protected]...
> > Okay, I've hit a snag with this. I just realized that this script only
> > seems
> > to be finding cells containing nothing but letters in all caps. Is there a
> > way to find cells containing some words in all caps and some in
> > upper/lowercase?
> >
> > "Jason Morin" wrote:
> >
> >> Select your data and run this macro:
> >>
> >> Sub OnlyUpper()
> >> Dim cell As Range
> >> For Each cell In Selection
> >> If cell.Value = UCase(cell.Value) Then
> >> cell.Font.ColorIndex = 3 'make font color = red
> >> End If
> >> Next
> >> End Sub
> >>
> >> ---
> >> HTH
> >> Jason
> >> Atlanta, GA
> >>
> >>
> >> "Nexan" wrote:
> >>
> >> > As part of a macro, I'd like to be able to identify and re-format only
> >> > cells
> >> > containing words in all caps. Is that doable?
> >> >
> >> > Thanks!
>
>
>

Don't know what information you have in your cells but something like this
could be used

For Each cell In Selection
If cell.Value = UCase(cell.Value) Then
cell.Font.ColorIndex = 3 'make font color = red
ElseIf cell.Value = LCase(Cell.Value) then
cell.Font.ColorIndex = 3 'make font color = red
ElseIf cell.Value = application.Proper(cell.Value) ' Matches if Proper
case, capital for each word eg This Is The End
ElseIf cell.VAlue like "[A-Z]*[A-Z]*"' Matched is Capital at start and
Capital some where in the text
'Code here
ElseIf cell.Value like "[A-Z]*" ' Matches value if it starts with a
capital letter
' Code here

End If
Next

Look in the help for examples of the Like Operator
Also not that for the Like Operator to Match Upper case letters you must
specify Option Compare Binary at the top of the module. If you Don't then
the case of the letters is ignored, i.e. A=a etc.

Hope this is useful

Nick


"Nexan" <[email protected]> wrote in message
news:[email protected]...
> Could you possibly give me an example of that?
>
> Thanks!
>
> "Nick" wrote:
>
>> You could use LCASE or the Like operator.
>>
>> Nick
>>
>>
>> "Nexan" <[email protected]> wrote in message
>> news:[email protected]...
>> > Okay, I've hit a snag with this. I just realized that this script only
>> > seems
>> > to be finding cells containing nothing but letters in all caps. Is
>> > there a
>> > way to find cells containing some words in all caps and some in
>> > upper/lowercase?
>> >
>> > "Jason Morin" wrote:
>> >
>> >> Select your data and run this macro:
>> >>
>> >> Sub OnlyUpper()
>> >> Dim cell As Range
>> >> For Each cell In Selection
>> >> If cell.Value = UCase(cell.Value) Then
>> >> cell.Font.ColorIndex = 3 'make font color = red
>> >> End If
>> >> Next
>> >> End Sub
>> >>
>> >> ---
>> >> HTH
>> >> Jason
>> >> Atlanta, GA
>> >>
>> >>
>> >> "Nexan" wrote:
>> >>
>> >> > As part of a macro, I'd like to be able to identify and re-format
>> >> > only
>> >> > cells
>> >> > containing words in all caps. Is that doable?
>> >> >
>> >> > Thanks!
>>
>>
>>