Select cell based on value of another cell

[1Marte]

My problem is probably very simple...I'm about to pull my hair out! I am using Excel2007.
I want to use the value that is in one cell to determine which of two cells to select in the worksheet. No copy, delete, just SELECT.
When I hover over the code it has the correct value, but the code doesnt work.
The bad code follows. I left the comments in so you can see what I've tried. nothing works.
THANK YOU in advance for any help. I have never posted before in my life, I have always found answers on this forum.

Sub FigFedWHcht()
'select the correct chart (Married or Single)
Sheets("Chart").Select '.Range("P6").Select
Dim rng As Range
'Set MCht = Sheet2.Range("B11")
'Set SCht = Sheet2.Range("R11")
Set rng = Sheet2.Range("P6")
With rng
'rng = rng.Value
Select Case rng
Case M
'MCht.Select
'Sheet2.Range("B11").Select
Sheet2.Range("FirstCellMarriedChrt").Select
Case S
'SCht.Select
'Sheet2.Range("R11").Select
Sheet2.Range("FirstCellSingleChrt").Select
'Case Else
' MsgBox ("There seems to be a problem") 'FOR TESTING
' GoTo xit
End Select
End With
Call FedFindResult
xit:
End Sub

 

[1Marte]

This worked for me last night before I went to bed. I think I have a couple of strands of hair left. I may have to just write code to always select Married as all my employees are married. Payroll was due yesterday. and I'm getting tired of code. I just wanted to confirm that I wasnt crazy. I'm totally self taught (about 6 years now). There must be something else in the background that is preventing this from working properly.

 

[jeffreybrown]

Just a thought...

Is it possible Application.EnableEvents could be turned off.

Open up the immediate window (Ctrl + G) >> Application.EnableEvents = True >> Enter

 

[1Marte]

Sorry to say, that didnt work either. I usually try not disable events if possible as i forget to write code to turn them back on and get into problems. Its my practice not to use application.enable events = false.
Just a quirk of mine.

 

[1Marte]

I have learned how to post code on this forum. and how to open the immediate window quickly. I dont use the immediate window as I cant wrap the concept of its use around my brain. I read and read, sometimes it sticks, sometimes....

 

[jeffreybrown]

Well I sure don't know what else it could be.

Did you try the code I attached or you just wanted confirmation?

You said it worked last night, but I noticed your M and S are not enclosed in "".

 

[1Marte]

I've enclosed them, unenclosed them. I pasted your code in. Nothing is working. Its got to be code running before this fires. I am trying to write this "masterpiece" using the column of the activecell.row , instead of selecting individual employees. That way I dont have to write code for "Sean" , same similar code for "Marte", etc. It works in what ever employees row I am working with. I got the code idea from this forum. Anytime I solve a problem or clear up messy code its with the know-how I walk away from here.
for example : Range("B" & (Activecell.row)) has really been a help. I went from 70 pages of written code for 3 employees to 10 pages.
I hope I didnt dent your armor of confidence. I'm walking away happy. I thank you so much for your help. You made my first experience at posting very pleasant.
THANK YOU JEFF!

 

[jeffreybrown]

You are most welcome and believe me I am learning right along with you