Excel-count character in cel

Posted on 2011-05-11
Last Modified: 2012-05-11
For a column I want to highlight cell that are more then 40 character ? The macro should work for a column only and not whole worksheet.

thak you for your help.
Question by:sud
    LVL 1

    Expert Comment

    Version of Excel?
    LVL 43

    Accepted Solution

    This will do it for the column in which the cursor is

    Sub highlightlong()
    For Each cel In ActiveCell.EntireColumn.Cells
    If Len(cel.Value) > 40 Then cel.Interior.ColorIndex = 6
    Next cel
    End Sub
    LVL 10

    Assisted Solution

    You can do this with conditional formatting.

    1. Highlight the cells where you want it
    2. choose "Format - Conditional Formatting"
    3. Change condition1 to say "Formula Is"
    4. Type in "=LEN(A2)>40" (without the quotes & replace "A2" with the first cell in your column)
    5. Choose a format by clicking on the format button
    6. Press OK
    LVL 33

    Expert Comment

    You can do it simply by using a Conditional Formatting on that column with a rule where you put a formula like this:


    Please take a look at the attached image and sample file.

    LVL 50

    Expert Comment

    I've requested that this question be closed as follows:

    Accepted answer: 500 points for answer_dude's comment http:/Q_27032341.html#35741519

    for the following reason:

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    The OP asked for a macro so that is what I gave. I should at least get a split....unless the OP changes his mind.
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    As above

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now