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
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?
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?
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.
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.
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
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
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)
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.