Capital letters

130 views
Skip to first unread message

Richard

unread,
Dec 18, 2002, 5:13:40 AM12/18/02
to
Could someone please tell me if there is a formula (placed in Conditional
Formatting) that will select all the capital letters in a column of text and
colour them red.
Thanks in advance
Richard


Mike

unread,
Dec 18, 2002, 5:54:49 AM12/18/02
to
Conditional formatting only works on the entire cell, not
individual characters. You will need a macro to be this
specific. Add this to your worksheet code. When ever you
change the contents of a cell in column 1 (A), this will
fire. It tests each character to see if it's upper case,
if so, set the font to red otherwise sets the font to
black. Just change the Target.Column to what ever column
you wanted to format. Hope this helps.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
For L = 1 To Len(Target.Value)
Target.Characters(Start:=L,
Length:=1).Font.ColorIndex = _
IIf(Mid(Target.Value, L, 1) = UCase(Mid
(Target.Value, L, 1)), 3, 1)
Next
End If

End Sub

>.
>

Richard

unread,
Dec 18, 2002, 6:29:22 AM12/18/02
to
Thank you Mike

"Mike" <mcu...@srjgroup.com> wrote in message
news:062b01c2a683$e29e0a90$d5f82ecf@TK2MSFTNGXA12...

Richard

unread,
Dec 18, 2002, 6:46:27 AM12/18/02
to
Mike

I am getting a syntax error on the line:

Private Sub Worksheet_Change(ByVal Target As Range)

It turns yellow, I take it this means it's where the error lies?


"Mike" <mcu...@srjgroup.com> wrote in message
news:062b01c2a683$e29e0a90$d5f82ecf@TK2MSFTNGXA12...

David McRitchie

unread,
Dec 18, 2002, 8:47:16 AM12/18/02
to
Hi Richard,
The lines get split up and you would need to use the
line continuation characters, space followed by underscore,
at the end of the continued line. Event macros are not
installed the same as regular macros. The change event
macro Mike supplied gets installed with the sheet.
Right click on the sheet tab, view code, plop the change
macro in.

The additional check for ISERROR will prevent the Change
Event from terminating when an error such as division by
zero is encountered.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

Dim L As Long


If Target.Column = 1 Then

If IsError(Target.Value) Then
Target.Font.ColorIndex = 14
Exit Sub
End If


For L = 1 To Len(Target.Value)

Target.Characters(Start:=L, _
Length:=1).Font.ColorIndex = _
IIf(Mid(Target.Value, L, 1) = UCase(Mid _


(Target.Value, L, 1)), 3, 1)
Next
End If
End Sub

When you use a Change Event to update or correct
new entries like Mike supplied, you may need a regular macro
to fixup existing entries.

Sub ColorCapsCatchUp()
Dim L As Long
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If IsError(cell.Value) Then
cell.Font.ColorIndex = 14
Else
For L = 1 To Len(cell.Value)
cell.Characters(Start:=L, _
Length:=1).Font.ColorIndex = _
IIf(Mid(cell.Value, L, 1) = UCase(Mid _
(cell.Value, L, 1)), 3, 1)
Next L
End If
Next cell
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Richard" <rgar...@jaguar.invalid> wrote in message news:atpn6j$lp...@eccws12.dearborn.ford.com...

Richard

unread,
Dec 18, 2002, 10:37:22 AM12/18/02
to
Thank you David

"David McRitchie" <dmcri...@msn.com> wrote in message
news:OXhtD6ppCHA.2376@tk2msftngp13...

David McRitchie

unread,
Dec 19, 2002, 8:55:12 AM12/19/02
to
A slight update on Mike's comparison. I think the intent
may have been to color ONLY the font of capital letters not
just anything that wasn't lowercase. So the comparison
might better be to check if the character differs between
LCASE and UCASE rather than treating numeric digits
the same as uppercase (not lowercase). The colorindex
of 3 is re=ad, and the colorindex of 1 is black.

IIf(Mid(LCase(Target.Value, L, 1)) <> UCase(Mid _
(Target.Value, L, 1)), 3,1)

rather than

IIf(Mid(Target.Value, L, 1) = UCase(Mid _
(Target.Value, L, 1)), 3, 1)

You're welcome Richard, and thanks again Mike for an
interesting solution.

thread reference:
http://google.com/groups?threadm=OXhtD6ppCHA.2376@tk2msftngp13

David McRitchie

unread,
Dec 19, 2002, 9:31:04 AM12/19/02
to
Sorry didn't test my solution here is the complete Change
macro to color ONLY uppercase letters Red (colorindex 3).
My untested solution was coloring all letters and only letters
regardless of capitalization.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim L As Long
If Target.Column = 1 Then
If IsError(Target.Value) Then
Target.Font.ColorIndex = 14
Exit Sub
End If
For L = 1 To Len(Target.Value)
Target.Characters(Start:=L, _
Length:=1).Font.ColorIndex = _

IIf(LCase(Mid(Target.Value, L, 1)) <> _
UCase(Mid(Target.Value, L, 1)) _
And Mid(Target.Value, L, 1) = _
UCase(Mid(Target.Value, L, 1)), 3, 1)


Next
End If
End Sub

and the complete code for the regular macro

Sub ColorCapsCatchUp()
Dim L As Long
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If IsError(cell.Value) Then
cell.Font.ColorIndex = 14
Else
For L = 1 To Len(cell.Value)
cell.Characters(Start:=L, _
Length:=1).Font.ColorIndex = _

IIf(LCase(Mid(cell.Value, L, 1)) <> _
UCase(Mid(cell.Value, L, 1)) _
And Mid(cell.Value, L, 1) = _
UCase(Mid(cell.Value, L, 1)), 3, 1)


Next L
End If
Next cell
End Sub

Interestingly will not work for cells formatted
with #,###.00;-#,###.00;;@ even though the cells
contain text not Formulas.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" <dmcri...@msn.com> wrote in message news:el$d8a2pCHA.704@TK2MSFTNGP09...

Reply all
Reply to author
Forward
0 new messages