Results 1 to 4 of 4

Thread: How to Go To a Conditional Formatted Cell

  1. #1

    Thread Starter
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    How to Go To a Conditional Formatted Cell

    I'm not much of an Excel user, and even less of a database person, but lately I've had the need to capture large amounts of data interchange between software modules and between hardware components.

    It is too much data to analyze by hand and rather than write a program to read and analyze the data (which might have been better in the long run), I figured I would format and paste the data into a spreadsheet and try to use that to do some quick analysis.

    For instance, I have collected raw data with a hardware analyzer over a period of time. I already know there is an 8-bit counter in one 32-bit word that should increment in sync with the 40hz transfer. I know that is isn't doing that reliably as sometimes the counter is stale and is the same value in two (or more) consecutive transfers.

    So, once I've formatted and pasted the data transfers into the spreadsheet, the 8-bit counter is isolated in one column of the spread sheet.
    I used a Conditional Formatting rule to highlight a cell if the next cell is the same value, i.e. (=E1=E2).
    This works fine and highlights all the cells in the column that meet that condition.

    The issue that I have is finding a way to find a cell that is highlighted in the column without having to page through the sheet looking for them visually. There are 10s of thousands of rows, and the error may occur only a few times in a small group of rows, with thousands of rows between occurrences.

    I don't know if a macro has to be defined in order to find the cell, or what. I've looked at various Find & Select options, but haven't figured out how to do a search for a cell in a column where the condition is true. It is probably a simple matter for those much more familiar with Excel than I am. It seems like it would be a common need, but having search the Internet for a while and plumbing around Excel I'm at a loss.

    I haven't tried writing a macro yet, as I'll have to learn a bit in order to do that, but it would certainly be worth learning. I thought I would ask here first as it seems like it should be something that is easily done by someone who uses Excel to do real work.
    Last edited by passel; Dec 23rd, 2021 at 03:19 PM.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: How to Go To a Conditional Formatted Cell

    If you Filter the sheet, Excel should allow you to Filter by Color, or Sort by Color. This may be all that you need.

    You can also create a VBA macro that can detect the color(s) and do whatever you want at that point. I generally have a color detector macro add a field to a column at the right of the sheet with whatever data I want, so I can then filter on that data as needed.

  3. #3
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: How to Go To a Conditional Formatted Cell

    passel,

    If you've got the Excel object model referenced, look up the WorksheetFunction object. You can get a tremendous amount of work done with that without actually ever putting anything into Excel cells. Just create your own Range objects and feed them into whatever Excel function you like via the WorksheetFunction object. Basically, any Excel function you want is available that way.

    EDIT: Just as an FYI, I do everything from VB6, but I do it late-bound (declaring all my Excel variables "As Object"). A nice trick though is to get it up and running via early-binding (so you've still got Intellisense) and then make it late-bound for distribution (even the Excel program, instantiating it with CreateObject("Excel.Application") for late binding. You could even use a compiler switch if you wanted. The late-binding just releases you from any specific version of Excel (and don't forget to de-reference your Excel object model if you want this).
    Last edited by Elroy; Dec 23rd, 2021 at 06:32 PM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  4. #4

    Thread Starter
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: How to Go To a Conditional Formatted Cell

    Elroy, I'm not working with an Excel Object from a program currently. I agree it may be more flexible, but I'm not sure if it would take more of a learning curve to get setup compared to just pasting the captured data into a spreadsheet in Excel and using Excel itself to explore the data. This is to aid in understanding the errors that are happening in the transmissions and where in the path of multiple layers of C++ code the data is generated from and passes through to get to the hardware and transmission that these errors may be introduced. Understanding if there is a pattern to the error in format or timing helps to deduce where such errors could be introduce.

    Quote Originally Posted by jdc2000 View Post
    If you Filter the sheet, Excel should allow you to Filter by Color, or Sort by Color. This may be all that you need.

    You can also create a VBA macro that can detect the color(s) and do whatever you want at that point. I generally have a color detector macro add a field to a column at the right of the sheet with whatever data I want, so I can then filter on that data as needed.
    I don't think a filter will work for me. I'm not interested in just the rows that have been conditionally formatted, but in the rows leading up to the errors and rows after the error.
    A filter could be useful for finding the time between occurrences, but I need all the rows around the anomalous rows to look for things like irregular timing of the transfers, e.g. if the 40hz transfers are a steady 40hz, rather than having lags and then surges to maintain an average of 40hz.

    I guess I'll have to work on a macro that will search down the column from whatever cell I'm in to find the next cell that has a fill color.
    When I describe the problems and the analysis done, it can be useful to share the worksheet in the meeting (remote meetings, sharing desktop or Window when presenting) and being able to jump to the first row of different sections in the spreadsheet where the errors occur would be helpful.

    I guess actually a filter could be used to gather the rows, then the first row in each "group" of errors I could manually put a number in a cell in a column to the right. I could then un-filter the rows, and select a cell in that column and use Ctrl-downArrow and Ctrl-upArrow to jump to up or down to the beginning of each group of errors.

    I guess I'll look at trying to create a filter first and doing that, and then work on a macro if I want for the experience.

    p.s. Filtering by color and then manually "tagging" the start row of each section was pretty simple and quick.
    Last edited by passel; Dec 24th, 2021 at 12:57 AM.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

Posting Permissions

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



Click Here to Expand Forum to Full Width