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
>.
>
"Mike" <mcu...@srjgroup.com> wrote in message
news:062b01c2a683$e29e0a90$d5f82ecf@TK2MSFTNGXA12...
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...
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...
"David McRitchie" <dmcri...@msn.com> wrote in message
news:OXhtD6ppCHA.2376@tk2msftngp13...
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
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...