Case sensitivity

J

JohnL

I have created a Private Sub Worksheet_Change(ByVal Target As Range), with a
Select Case argument. When the value of cell D6 is equal to “HOUSEâ€, then
the interior color of cell A8 becomes yellow.
But if the user enters “House†in cell D6, then it doesn’t work. How can I
overcome case sensitivity?

TIA

JohnL
 
R

Rick Rothstein

It is always a good idea to show the code you are using for the problem area
you are asking your question about. My guess is for you to use the UCase
function on the argument to the Select Case statement and then test the
individual Case statements against upper case text. For example...

Select Case UCase(Target.Value)
Case "HOUSE"
....
Case "ROOM"
....
etc.
End Select
 
J

JohnL

Rick, what it looks looks like is this:

Select Case Range("D6").Value

Case "HOUSE"
Range("A8â€).Interior.ColorIndex = 6
Range("A8").Font.ColorIndex = 2
 
D

Dave Peterson

Notice Rick's suggestion included ucase().

Select Case UCase(Target.Value)

So you could use:

Select Case ucase(Range("D6").Value)

Another alternative if you don't care about any case comparisons...

Add this line to the top of the module:

Option Compare Text
 
J

JohnL

Thanks Dave. I was confused with (Target.Value) but your line was very
explanatory. My Worksheet Sub works like a charm now. Couldn't use your
second suggestion because this isn't in a module.
Thanks Rick and Dave for your time. I always learn things at this site.
JohnL
 
R

Rick Rothstein

I used Target.Value for two reasons... one, you showed you were using a
Change event procedure and so I guessed you were working with the Target
argument that the procedure automatically references and, two, you didn't
post your code originally so I didn't know what you were using for the
argument to the Select Case statement. As I said originally, "It is always a
good idea to show the code you are using for the problem area you are asking
your question about" and the reason is because we can tailor our response to
it and you won't be left scratching your head as to how to change our
solutions based on a guessed-at condition.

--
Rick (MVP - Excel)


JohnL said:
Thanks Dave. I was confused with (Target.Value) but your line was very
explanatory. My Worksheet Sub works like a charm now. Couldn't use your
second suggestion because this isn't in a module.
Thanks Rick and Dave for your time. I always learn things at this site.
JohnL
 
D

Dave Peterson

It's in a worksheet module.

The "option compare text" will work in that kind of module, too.
Thanks Dave. I was confused with (Target.Value) but your line was very
explanatory. My Worksheet Sub works like a charm now. Couldn't use your
second suggestion because this isn't in a module.
Thanks Rick and Dave for your time. I always learn things at this site.
JohnL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top