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

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

--
Rick (MVP - Excel)


"JohnL" <(E-Mail Removed)> wrote in message
news:3D842859-A202-4FB1-9719-(E-Mail Removed)...
>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
>

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


"Rick Rothstein" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>

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



JohnL wrote:
>
> 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
>
> "Rick Rothstein" wrote:
>
> > 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
> >
> > --
> > Rick (MVP - Excel)
> >

--

Dave Peterson

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

"Dave Peterson" wrote:

> 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
>
>
>
> JohnL wrote:
> >
> > 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
> >
> > "Rick Rothstein" wrote:
> >
> > > 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
> > >
> > > --
> > > Rick (MVP - Excel)
> > >
>
> --
>
> Dave Peterson
>

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" <(E-Mail Removed)> wrote in message
news:A58EEC7B-FAE4-4AE5-8C3A-(E-Mail Removed)...
> 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
>
> "Dave Peterson" wrote:
>
>> 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
>>
>>
>>
>> JohnL wrote:
>> >
>> > 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
>> >
>> > "Rick Rothstein" wrote:
>> >
>> > > 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
>> > >
>> > > --
>> > > Rick (MVP - Excel)
>> > >
>>
>> --
>>
>> Dave Peterson
>>

It's in a worksheet module.

The "option compare text" will work in that kind of module, too.

JohnL wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > 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
> >
> >
> >
> > JohnL wrote:
> > >
> > > 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
> > >
> > > "Rick Rothstein" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Rick (MVP - Excel)
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson