Auto change title in column 'X'

[mukeshbaviskar]

Please suggest me a code to auto change title in column 'x' base on the following condition:

Column 'c' title is selected 'Mr.' and in column 'u' marital status selected ' Married' then the title in column 'x' should be change to 'Mrs.' automatically.

Column 'c' title 'Mrs.' is selected and in column 'u' marital status is 'Married' is selected then in column 'x' should be change to 'Mr.'

column 'c' 'Mr. or Mrs.' is selected and in column 'u' 'Unmarried' is selected then the column 'x' should be change to '-' automatically.

If '-' is selected in column 'u' then the column 'x' should be change to '-' automatically (no matter what is in column 'c')

If '-' is selected in column 'c' then the column 'u and x' should be change to '-' automatically.

Please help me to achieve this target automatically.

Thanking you in anticipation.

Mukesh

[christopherL]

Hey, try this

Please Login or Register  to view this content.

[:) Sixthsense :)]

Whether this should happen to all cells in the whole columns or for any particular cells only?

If it is particular cells then provide us the cell references.

[christopherL]

Don't really get what you mean with selected..

[mukeshbaviskar]

Hi christopherL,
Thank you for suggestion. It's giving error 1004. Actually I want to insert the code in sheet module fo automatic action. I apologize for not attaching a sample file for your reference.

Now I'm attaching a sample file for your reference. Please go through it and give me a solution. There are already few codes for other functions in it. Add this code there.

Thank you.

Mukesh

[christopherL]

Sorry about that,
This works for me.

Please Login or Register  to view this content.

If you are happy with your result please mark the thread "Solved" in thread settings,
and if you are please with your response please add reputation!

Regards C

Hi christopherL,
Thank you for suggestion. It's giving error 1004. Actually I want to insert the code in sheet module fo automatic action. I apologize for not attaching a sample file for your reference.

[mukeshbaviskar]

No! I want a code to work automatically after selecting the selection. Please see my file again to correct it as per my requirement.

Sorry for trouble.

Thank you.

Mukesh
No, I want a code to work automatically. I don't want to do it manually.

Please see my file again to modify it as per my requirement.

Sorry for trouble.

Thank you.

Mukesh

[christopherL]

You put the macro in the module attached to the workbook (in the VBA editor, doubleclick on ThisWorkbook in the properties window) . Call the macro Workbook_Open(), and it will run whenever the workbook is first opened.

[mukeshbaviskar]

No, I don't want to use call event for it because there are already some other macros in this workbook code. I want the output while entering the data because otherwise the user may confuse whether the selection is changed after opening the file or not.

I want to create case 25 column 'x' for this function.

Please help me.

Thank you.

Mukesh

[mukeshbaviskar]

I have inserted the code in sheet module 'case 24' but it is giving error. Please go through it and give me a solution.
Please see the attachment.

Thank you.

Mukesh

[alanga]

Hi Mukesh

How about formula instead of VB. So this formula in X9 and copied down

=IFERROR(IF(U9="Married",VLOOKUP(C9,{"Mr.","Mrs.";"Mrs.","Mr."},2,0),"-"),"-")

[mukeshbaviskar]

Hi alanga,
Excellent! What a tricky formula. It's working fine.

Thank you very much for solving my problem.

Have a nice day.

Mukesh

[mukeshbaviskar]

Hi Alanga,
Sorry, I need a little modification in the formula as follows:

If there is nothing selected in column 'c' and column 'u' then the column 'x' should be blank.

I tried to add ', ""' at the end but it's giving error. Please correct the formula and solve the problem.

Thank you.

Mukesh

[alanga]

post deleted, need to rethink

[alanga]

So I added in the bit in red. Not very nice but I think it does the trick.

=IF(COUNTA(C9,U9)=2,IFERROR(IF(U9="Married",VLOOKUP(C9,{"Mr.","Mrs.";"Mrs.","Mr."},2,0),"-"),"-"),"")

[mukeshbaviskar]

Dear Alanga,
Excellent! It's a very tricky and complicated formula you have created. It solved my problem.

Congratulation and thank you once again.

Have a nice day.

Mukesh