Forcing Upper Case

Column G has a conditional format for Green, Yellow, Red depending on
the content of G, Y, R. It serves as a "Stop Light" indicator.

I need to force this to upper case or some of the other cell formulas
do not work. (it recognizes G but not g etc.

I created a sub in the "Workbook_SheetChange" event to stuff the cell
with an uppercase.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ThisRow As Integer, ThisCol As Integer
Application.EnableEvents = False
ThisRow = ActiveCell.row
ThisCol = ActiveCell.Column
Cells(ThisRow, ThisCol).Value = UCase(Target)
Application.EnableEvents = True

End Sub

Problem - The GYR resides in a merged area of 3 to 6 rows. When I step
thru the macro, I see that Target captures the value (say "g") which is
in merged rows 4-9. Also "ThisRow" has aready changed to row 10 (also
a merged area of rows 10-12). Therefore the the code to insert
UCase(Target) does so on row 10 instead of row 4 (or whatever the
originating row is).

How can I capture the originating row? I can't simply subtract a
constant because the merged rows are not always the same.

Glen

try
Private Sub Worksheet_Change(ByVal Target As Range)
Target = UCase(Target)
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<glen.e.mettler@lmco.com> wrote in message
news:1142607611.459031.310450@u72g2000cwu.googlegroups.com...
> Column G has a conditional format for Green, Yellow, Red depending on
> the content of G, Y, R. It serves as a "Stop Light" indicator.
>
> I need to force this to upper case or some of the other cell formulas
> do not work. (it recognizes G but not g etc.
>
> I created a sub in the "Workbook_SheetChange" event to stuff the cell
> with an uppercase.
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> Range)
> Dim ThisRow As Integer, ThisCol As Integer
> Application.EnableEvents = False
> ThisRow = ActiveCell.row
> ThisCol = ActiveCell.Column
> Cells(ThisRow, ThisCol).Value = UCase(Target)
> Application.EnableEvents = True
>
> End Sub
>
> Problem - The GYR resides in a merged area of 3 to 6 rows. When I step
> thru the macro, I see that Target captures the value (say "g") which is
> in merged rows 4-9. Also "ThisRow" has aready changed to row 10 (also
> a merged area of rows 10-12). Therefore the the code to insert
> UCase(Target) does so on row 10 instead of row 4 (or whatever the
> originating row is).
>
> How can I capture the originating row? I can't simply subtract a
> constant because the merged rows are not always the same.
>
> Glen
>

Don, Such a simple solution. Thanks! Glen

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Column = 7 'Column G then
Target = UCase(Target)
end if
End Sub

You probably don't want your whole sheet to be uppercase!

HTH
--
AP


"Don Guillett" <dguillett1@austin.rr.com> a écrit dans le message de
news:uX4nJedSGHA.256@TK2MSFTNGP14.phx.gbl...
> try
> Private Sub Worksheet_Change(ByVal Target As Range)
> Target = UCase(Target)
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> <glen.e.mettler@lmco.com> wrote in message
> news:1142607611.459031.310450@u72g2000cwu.googlegroups.com...
> > Column G has a conditional format for Green, Yellow, Red depending on
> > the content of G, Y, R. It serves as a "Stop Light" indicator.
> >
> > I need to force this to upper case or some of the other cell formulas
> > do not work. (it recognizes G but not g etc.
> >
> > I created a sub in the "Workbook_SheetChange" event to stuff the cell
> > with an uppercase.
> >
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> > Range)
> > Dim ThisRow As Integer, ThisCol As Integer
> > Application.EnableEvents = False
> > ThisRow = ActiveCell.row
> > ThisCol = ActiveCell.Column
> > Cells(ThisRow, ThisCol).Value = UCase(Target)
> > Application.EnableEvents = True
> >
> > End Sub
> >
> > Problem - The GYR resides in a merged area of 3 to 6 rows. When I step
> > thru the macro, I see that Target captures the value (say "g") which is
> > in merged rows 4-9. Also "ThisRow" has aready changed to row 10 (also
> > a merged area of rows 10-12). Therefore the the code to insert
> > UCase(Target) does so on row 10 instead of row 4 (or whatever the
> > originating row is).
> >
> > How can I capture the originating row? I can't simply subtract a
> > constant because the merged rows are not always the same.
> >
> > Glen
> >
>
>

OOooops... typo!

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Column = 7 then 'Column G
Target = UCase(Target)
end if
End Sub

"Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message de
news:%23ubSz5dSGHA.1728@TK2MSFTNGP11.phx.gbl...
> Private Sub Worksheet_Change(ByVal Target As Range)
> if Target.Column = 7 'Column G then
> Target = UCase(Target)
> end if
> End Sub
>
> You probably don't want your whole sheet to be uppercase!
>
> HTH
> --
> AP
>
>
> "Don Guillett" <dguillett1@austin.rr.com> a écrit dans le message de
> news:uX4nJedSGHA.256@TK2MSFTNGP14.phx.gbl...
> > try
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Target = UCase(Target)
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > <glen.e.mettler@lmco.com> wrote in message
> > news:1142607611.459031.310450@u72g2000cwu.googlegroups.com...
> > > Column G has a conditional format for Green, Yellow, Red depending on
> > > the content of G, Y, R. It serves as a "Stop Light" indicator.
> > >
> > > I need to force this to upper case or some of the other cell formulas
> > > do not work. (it recognizes G but not g etc.
> > >
> > > I created a sub in the "Workbook_SheetChange" event to stuff the cell
> > > with an uppercase.
> > >
> > > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
> > > Range)
> > > Dim ThisRow As Integer, ThisCol As Integer
> > > Application.EnableEvents = False
> > > ThisRow = ActiveCell.row
> > > ThisCol = ActiveCell.Column
> > > Cells(ThisRow, ThisCol).Value = UCase(Target)
> > > Application.EnableEvents = True
> > >
> > > End Sub
> > >
> > > Problem - The GYR resides in a merged area of 3 to 6 rows. When I
step
> > > thru the macro, I see that Target captures the value (say "g") which
is
> > > in merged rows 4-9. Also "ThisRow" has aready changed to row 10 (also
> > > a merged area of rows 10-12). Therefore the the code to insert
> > > UCase(Target) does so on row 10 instead of row 4 (or whatever the
> > > originating row is).
> > >
> > > How can I capture the originating row? I can't simply subtract a
> > > constant because the merged rows are not always the same.
> > >
> > > Glen
> > >
> >
> >
>
>

Adrus,

You should disable events in this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False '<<<<<
if Target.Column = 7 then 'Column G
Target = UCase(Target)
end if
Application.EnableEvents = True
End Sub

If you don't disable events, you'll get in to an infinite loop.
The Change procedure changes a value, which causes Change to run
(again), which changes a value, which causes Change to run, which
changes a value, which causes Change to run, ad infinitum.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Ardus Petus" <ardus.petus@laposte.net> wrote in message
news:%23hD%23f%23dSGHA.4440@TK2MSFTNGP11.phx.gbl...
> OOooops... typo!
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if Target.Column = 7 then 'Column G
> Target = UCase(Target)
> end if
> End Sub
>
> "Ardus Petus" <ardus.petus@laposte.net> a écrit dans le message
> de
> news:%23ubSz5dSGHA.1728@TK2MSFTNGP11.phx.gbl...
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> if Target.Column = 7 'Column G then
>> Target = UCase(Target)
>> end if
>> End Sub
>>
>> You probably don't want your whole sheet to be uppercase!
>>
>> HTH
>> --
>> AP
>>
>>
>> "Don Guillett" <dguillett1@austin.rr.com> a écrit dans le
>> message de
>> news:uX4nJedSGHA.256@TK2MSFTNGP14.phx.gbl...
>> > try
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > Target = UCase(Target)
>> > End Sub
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > dguillett1@austin.rr.com
>> > <glen.e.mettler@lmco.com> wrote in message
>> > news:1142607611.459031.310450@u72g2000cwu.googlegroups.com...
>> > > Column G has a conditional format for Green, Yellow, Red
>> > > depending on
>> > > the content of G, Y, R. It serves as a "Stop Light"
>> > > indicator.
>> > >
>> > > I need to force this to upper case or some of the other
>> > > cell formulas
>> > > do not work. (it recognizes G but not g etc.
>> > >
>> > > I created a sub in the "Workbook_SheetChange" event to
>> > > stuff the cell
>> > > with an uppercase.
>> > >
>> > > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
>> > > Target As
>> > > Range)
>> > > Dim ThisRow As Integer, ThisCol As Integer
>> > > Application.EnableEvents = False
>> > > ThisRow = ActiveCell.row
>> > > ThisCol = ActiveCell.Column
>> > > Cells(ThisRow, ThisCol).Value = UCase(Target)
>> > > Application.EnableEvents = True
>> > >
>> > > End Sub
>> > >
>> > > Problem - The GYR resides in a merged area of 3 to 6 rows.
>> > > When I
> step
>> > > thru the macro, I see that Target captures the value (say
>> > > "g") which
> is
>> > > in merged rows 4-9. Also "ThisRow" has aready changed to
>> > > row 10 (also
>> > > a merged area of rows 10-12). Therefore the the code to
>> > > insert
>> > > UCase(Target) does so on row 10 instead of row 4 (or
>> > > whatever the
>> > > originating row is).
>> > >
>> > > How can I capture the originating row? I can't simply
>> > > subtract a
>> > > constant because the merged rows are not always the same.
>> > >
>> > > Glen
>> > >
>> >
>> >
>>
>>
>
>