Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Create function to color if line over # of characters

Posted on 2013-11-16
Medium Priority
Last Modified: 2014-11-12
Multiple lines can appear in a single cell.  I need a function since it is a shared Google doc and not a traditional Excel sheet.  The function should highlight any cells that contain a line over 20 characters.

Assistance is greatly appreciated.
Question by:frugalmule
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 22

Assisted Solution

rspahitz earned 2000 total points
ID: 39654077
You can do thris through conditional formatting.
Select the column which has the text, such as A and go to conditional formatting: menu Home | Styles Conditional Formatting
Then select New Rule and pick Use a formula to determine which cells to format
In the text box enter: =LEN(A1)>20
Below that, pick a format, such as setting the text red and click OK until you're back on the spreadsheet.
Any cell with more than 20 characters will highlight as you specified.

Author Comment

ID: 39654083
The problem is that any 'cell' with more than 20 characters does not meet the requirement where any 'line' with more than 20 characters would meet the requirement.
LVL 22

Assisted Solution

rspahitz earned 2000 total points
ID: 39654871
Let's see what I can come up with...

Try changing the conditional format condition to this:


Open in new window

Then add this in a new VB module (Alt+F8, menu Insert | Module):
Function LongestLine(Cell As Range) As Integer
    Dim iLongestLine As Integer
    Dim strLines() As String
    Dim iLineCntr As Integer
    strLines = Split(Cell, vbLf)
    iLongestLine = 0
    For iLineCntr = 0 To UBound(strLines)
        If Len(strLines(iLineCntr)) > iLongestLine Then
            iLongestLine = Len(strLines(iLineCntr))
        End If
    LongestLine = iLongestLine
End Function

Open in new window

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.


Author Comment

ID: 39654895
The purpose of Google Docs is for sharing and the sharing component is far greater than what Office 360 can provide.

However, it limits us in some other ways.  This is the extent of what a google spreadsheet can do without a java script I think

Author Comment

ID: 39654905
Here is a depiction of the scripting capabilities because we are limited in the traditional sense
LVL 22

Accepted Solution

rspahitz earned 2000 total points
ID: 39654937
So it appears you are looking for a GoogleDocs solution (but since you created the question in the Excel section, I thought you were looking for Excel.)
So I'm not really familiar with Google's spreadsheets, but based on your link I see this:

Text      SPLIT      SPLIT(string, delimiter, treat_delimiters_individually)

  This will be a bit of work, but you should be able to use this to check to see if any of the resulting items has a length > 20.  Alternately, you can use the FIND function to repeatedly search for the "newline" delimiter (which might be Code(13) or Code(10) or a combination of Code(13)&Code(10), depending on the source of the data.

I hope that using this info and the script I shared above, you can make it work.

Author Comment

ID: 39654952
I may have an alternate solution.  I think I can just reduce the columns and be able to tell which ones need changing that way.  Thanks for the tips.
LVL 22

Expert Comment

ID: 39654960
You're welcome...I guess I need to start learning google sheets. use Code 13, you may need to use CHAR(13) in the split

Author Comment

ID: 39655004
Yeah, I really like them.

It would be nice to use macros but the ability to share comments on each cell and even restrict editing based on a range is pretty big plus for us.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

705 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