Forcing Upper Case

[glen.e.mettler]

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 Guillett]

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

 

[glen.e.mettler]

Don, Such a simple solution. Thanks! Glen

 

[Ardus Petus]

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

 

[Ardus Petus]

OOooops... typo!

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

 

[Chip Pearson]

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