Automatically conditionally format font size based on text length

[rh2705]

Greetings

I have a staff rota spreadsheet which shows D for rostered days N for nightshift H for holiday and various other abbreviations and I want the cell to automatically change font size for the following conditions:
text length <=2 I want font size to be 11
text length = 3 I want the font size to be 10 and
text length >3 I want the font size to be 5

Being a VBA 'feartie' (I am trying here but its so difficult,dont get me wrong I just love wasting the time messing with VBA but I GIVE UP on this one :-( ) I wonder if anyone might be able to help. I've googled for some ready made code for this and found similar solutions that I have tried to adapt with zero success. What I have learned is:

I need a 'ByVal Target is Range' statement
I need to declare and dimension a variable as a range object which I have called 'cr'
I think a case statement will do the trick.

I've got as far as:

Please Login or Register  to view this content.

Which doesn't create any vba error messages, HOWEVER, when I run the code ... Dan Dan Daaaaaannnnnn!!!!! .... NOTHING Happens

All help, advice, suggestions very gratefully received.

TIA

Rob

[JBeaucaire]

Something like this:

Please Login or Register  to view this content.

[rh2705]

Thank you so much! I've been struggling with this for days now and finally at last I can move on with it. So simple!! I understand all the code except the 'If Not Intersect (Target, Range...' code up to the case statement, but thats where the really interesting fun starts for me - figuring out what it actually means - great fun :-)

You have made my night!

Cheers

Rob

[JBeaucaire]

In simple terms it checks to make sure there WAS a change in column B. If there wasn't a change in column B, nothing happens. If there was, it cycles through all the cells that were changed in column B (that's the intersect) one at a time and runs your font code on them.

If that takes care of your posted question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.