Capital letters

[Richard]

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]

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]

Thank you Mike

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

[Richard]

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]

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]

Thank you David

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

[David McRitchie]

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]

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