+ Reply to Thread
Results 1 to 11 of 11

Change font color based on character count

  1. #1
    Registered User
    Join Date
    04-20-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    5

    Change font color based on character count

    Hello Guys,

    I need to count the charcters and change its color if its exceeds the maximum count.For example, if the maximum count is 70 characters including space and the entered value is of 110 charcters, then the first 70 letters should change the font color.I have been trying a formula for this, but all in vain.
    Thanks In Advance for your help!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Change font color based on character count

    There isn't a formula to change font colour, so that's doomed to failure, I'm afraid.

    You can use conditional formatting to set the whole cell to red if the length exceeds 70 characters (see column A in the attached workbook), but if you want only the first 70 characters in red then you'll need some VBA code.

  3. #3
    Registered User
    Join Date
    04-20-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Change font color based on character count

    Thanks Andrew for the kind reply, But i needed to change the colour of maximum count characters only if it exceeds the maximum count. I mean, the font colour of first 70 characters only should be changed, rest of the characters should not be, if the user exceeds the maximum count which is 70.

  4. #4
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Change font color based on character count

    Hi

    If you don't want, user to put more than 70(per example) characters, then to can(using Custom Validation), force him(her) to TYPE <=70 characters in this cell.

    Or else(as Andrew told), you need VBA, for this.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Change font color based on character count

    Hi look at such code

    Please Login or Register  to view this content.
    it works in cells(1,1) but You can change this into event procedure
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  6. #6
    Registered User
    Join Date
    04-20-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Change font color based on character count

    Hi tom,

    Actually i am not much aware of this VBA, can you please tell me how to execute this code.

    Thanks

  7. #7
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Change font color based on character count

    Ok open new workbook press ALT + F11 insert new module paste this code into it then type in cell A1 string longer than 70 characters and run this macro. (e.g. by pressing F5 when code is visible)
    I hoop You understood because I can't make it clearer.

  8. #8
    Registered User
    Join Date
    04-20-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Change font color based on character count

    Thanks Tom, now i get it. But can make it work for a single coloumn, since current code works for only cel(1,1)....:'(

  9. #9
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Change font color based on character count

    Hi use this in module of workseet
    Please Login or Register  to view this content.
    so You have to right click on worksheet's name then click "view code" and paste this code in window which appears

  10. #10
    Registered User
    Join Date
    04-20-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Change font color based on character count

    Hi Tom, thanks this code Works.
    But the problem is,if you delete the characters, after entering more than the count.It wont change to old colour.It still remains in red colour only,even the count is less than 70.

  11. #11
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Change font color based on character count

    so then.......

    Please Login or Register  to view this content.
    and thx for rep points

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1