+ Reply to Thread
Results 1 to 8 of 8

Detecting black spaces within red strings and highlighting the entire cell

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    197

    Detecting black spaces within red strings and highlighting the entire cell

    I'm analysing a large datasheet of over 80k rows which extends over 7 columns A-G. Data are grouped into sets which are separated by one empty row. Data sets vary dramatically in size which is the number of their rows.

    ColD is the main working area at the moment, where it contains strings with two colours: black and red.

    Red portion of each cell's string should all be red to enable further analysis, but, in fact, there are some invisible black spaces in that red portion of the cell string which cause errors in my analysis.

    What I am after is to test all cells red portion of the text and if there's any invisible black spaces, then cells should be highlighted in yellow.

    I've attached a simple example, in which Sheet1 contains three manually created black spaces in the red portion of diffferent cells, and Sheet2 is the required result after detecting the errors.

    Can I get some precious help with this problem, please?

    Many thanks in advance ..

    T.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    688

    Re: Detecting black spaces within red strings and highlighting the entire cell

    With over 80,000 rows of data, I suspect this code will take quite a bit of time to execute, so be patient...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 11-20-2019 at 01:55 AM.

  3. #3
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    197

    Re: Detecting black spaces within red strings and highlighting the entire cell

    Hi Rick,
    Thank you very much for your solution, which I really appreciate. I tested the code on a 1k of rows and it ran smoothly, which made me to run it on a 40k portion of the data. For over 3 hours, and until typing these words, the code is still running .. is this normal? Should I be more patient until it finishes, but when? If there's any suggestions to speed the processing up I'm more than appreciating.
    Once again, thank you very much indeed.

    T.

  4. #4
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    688

    Re: Detecting black spaces within red strings and highlighting the entire cell

    The problem is that the code is literally examining the color of every character, one at a time, in every cell in Column D. You ran it on 40,000 rows of data which varies in length, so yes, it takes a long time to execute. I have some ideas on how to speed it up somewhat, but do not look for a quick solution as the code below will still have to examine a large percentage of the individual characters within each cell. Anyway, give this a try and see if the number of hours to complete is reduced noticeably or not.
    Please Login or Register  to view this content.
    Note: If you stop the code before it completes, your Excel worksheet will appear frozen. If that happens, run these two code lines, one at a time, in the VB editor's Immediate Window to restore normal operation..
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    4,029

    Arrow

    Hi !

    A smarter way instead of slow color analysis may be a column for the first word # and the # words
    like for example according to the attachment for cell D1 2,3 (3 words startin' from word #2) or even 2 columns

  6. #6
    Valued Forum Contributor
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    688

    Re: Detecting black spaces within red strings and highlighting the entire cell

    Quote Originally Posted by Marc L View Post
    A smarter way instead of slow color analysis may be a column for the first word # and the # words
    like for example according to the attachment for cell D1 2,3 (3 words startin' from word #2) or even 2 columns
    That is a good idea, if... I got the impression that the text presented in Message #1 was made up for example purposes and that within the 80,000 row database that the OP indicated this code was for, that the actual text as well as red text will vary greatly. Of course, I don't know that for sure, so hopefully when the OP checks back in, he can clarify whether the text has any kind of regular pattern to it.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    4,029

    Arrow

    Yes it was just a sample among many others like as another example a list for words sequences.

    'Unfreezing' during execution adding a DoEvents statement for each hundred iterations for example

  8. #8
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    197

    Re: Detecting black spaces within red strings and highlighting the entire cell

    Thank you very much Rick for your "first" solution as I left it working all night and it did actually do the job as exactly as desired through that huge amount of data. I haven't got the chance to run the second code as I've just "regained" my excel.
    With all due respect, thank you very much.

    T.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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