VBA to change cell alignment

hi,

I have searched but have not found...please help.

I would like the contents of cell C1, which happens to be numbers, to align left if cell A1=0, align centre if cell A1 =1 and align Right if cell A1 =2. I realize this must be terribly easy...thanx a lot for any help..


I STILL CANNOT SEEM TO REPLY TO MY GOOD SAMARITANS...I HOPE YOU GUYS SEE THIS SOMEHOW

THANX TO BURAN, RIORAN AND JOHN DAVIS. YOU HAVE ALL BEEN A GREAT HELP!!

OKAY..LET ME BE SPECIFIC...I USED BURAN'S IDEA...WORKS GREAT..THANX A LOT!!

Hello!

When do you expect for alignment to change? By button click or when any part of worksheet is changed?

Something like this

Please Login or Register  to view this content.

Note that it will be aligned right for any value <> 0 or 1

I think combination of subroutines will do better =)

If you have to change A1 manually - then it could be better not to rely on sheet events.
Check buttons in the attachment, may be you will like it.

Please Login or Register  to view this content.

Originally Posted by Rioran

If you have to change A1 manually - then it could be better not to rely on sheet events

if user has to click a custom button, then the macro more or less is not necessary - you can click the RibbonX as well with the same effect. :-)

Originally Posted by buran

if user has to click a custom button, then the macro more or less is not necessary - you can click the RibbonX as well with the same effect. :-)

Yes, but in case of my buttons we have 1 click economy - you don't need to care about selecting a target cell =)

Originally Posted by Rioran

Yes, but in case of my buttons we have 1 click economy - you don't need to care about selecting a target cell =)

well if it's about click economy, using sheet event like I did is the ultimate winner here :-)

Originally Posted by buran

well if it's about click economy, using sheet event like I did is the ultimate winner here :-)

Congratulations, then! =) But if it were amount-of-steps-contest... Hm... Let me think... To select A1 cell, to type value you need and filanlly to hit the Enter button... 3 steps against single button click =)

I think we have to wait for author to reveal his or her eternal wishes... Wow, stop, all of us are helpful - see the 1-st message.

Originally Posted by Rioran

Congratulations, then! =) But if it were amount-of-steps-contest... Hm... Let me think... To select A1 cell, to type value you need and filanlly to hit the Enter button... 3 steps against single button click =)

I think we have to wait for author to reveal his or her eternal wishes... Wow, stop, all of us are helpful - see the 1-st message.

No we are not in a amount of steps contest. It's not necessary that user has to change a1 manually. it could be some sort of formula there. That's the reason why using event is better - it's independent of the user and alignment is "always" as it is expected to be. In your case (no matter how A1 has changed) human factor is always present - e.g. user who simply forgets to click the button...
anyway - I'm not going in so much trouble without reason or to "prove" which solution is better. There are no "wrong" solutions anyway. I think it's useful for the person who ask as well as the other users who may come to this thread searching for similar solution, because it shows the benefits of each approach.