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

Hi T.

many thanks for the help, yes it worked
Noel


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"NoelH" wrote in message
...
Hi Jason
I have found this old script and yes it works for me, However is it
possible
to have an addition part so that in addition to the Red colour, addtional
text text is added to another column.
Eg say the selection is in Column A, if the CAPS word is found in cell A2
then B2 has '1' placed in it. This would allow a filter to be setup on Col
B.

Many thanks for any help


"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!

Hi Don
Thanks for the help
Yes this too worked.

Noel


"Don Guillett" wrote:

If cell.Value = UCase(cell.Value) Then
cell.offset(,1)=1
cell.Font.ColorIndex = 3 'make font color = red
End If


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NoelH" wrote in message
...
Hi Jason
I have found this old script and yes it works for me, However is it
possible
to have an addition part so that in addition to the Red colour, addtional
text text is added to another column.
Eg say the selection is in Column A, if the CAPS word is found in cell A2
then B2 has '1' placed in it. This would allow a filter to be setup on Col
B.

Many thanks for any help


"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!

Hi Gord
Thanks for the help
Yes this worked too.
Noel

"Gord Dibben" wrote:

Not Jason but here's a revision of his code.

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


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 15:55:01 -0800, NoelH
wrote:

Hi Jason
I have found this old script and yes it works for me, However is it possible
to have an addition part so that in addition to the Red colour, addtional
text text is added to another column.
Eg say the selection is in Column A, if the CAPS word is found in cell A2
then B2 has '1' placed in it. This would allow a filter to be setup on Col B.

Many thanks for any help


"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're welcome. Thanks for the feedback!

Note that I added a test for empty cells. The other replies did not. An
empty cell would get a 1. (if you might have empty cells in the range)

--
Biff
Microsoft Excel MVP


"NoelH" wrote in message
...
Hi T.

many thanks for the help, yes it worked
Noel


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"NoelH" wrote in message
...
Hi Jason
I have found this old script and yes it works for me, However is it
possible
to have an addition part so that in addition to the Red colour,
addtional
text text is added to another column.
Eg say the selection is in Column A, if the CAPS word is found in cell
A2
then B2 has '1' placed in it. This would allow a filter to be setup on
Col
B.

Many thanks for any help


"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!