Shade rows based on grouped column data

Posted on 2014-02-17
Last Modified: 2014-02-19
Hi.  I am including a report consisting of data imported from a database.  I am looking for a way to look at each row and perform some formatting based on data in each row.

1st 2 columns are ORDNO and ORDSTAT.  I ran a macro to merge cells in those two columns if a row had the same data as the previous row of data.  That creates a grouping by ORDERNO and ORDSTAT.  Within each grouping, I'd like to automatically shade each alternate group and I'd like to highlight every instance within in each group where OPSTAT is greater than the previous OPSTAT value.  

I've included a report where I've manually filled the first page or two with the shading and highlighting I'm trying to automate throughout the entire report.  Hope this makes sense to someone ...
Question by:valmatic
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
  • 3
  • 2
LVL 12

Expert Comment

by:Harry Lee
ID: 39866227

1) Can you upload your macro, with what you have on the sample workbook, I think it's best to work with the macro.

2) When you mention highlighting every instance within each group where OPSTAT is greater than the previous OPSTAT;

On row 3, how is 10 greater than row 2, 30?
LVL 31

Expert Comment

ID: 39866986
I am sorry,

but don't you find that your coloring is not 'CLEAR'

Just by the meer fact that by looking at the shading it is not clear what you did and what you expect to have that it is enough for maybe revising what you need. I read your post 5 times already and looked at the file to be able to understand:

When a line should be white background
When a line should be grey background
When a line should be yellow background

I am not able to know. so therefore will not be able to draw a macro.

Maybe if you re-explain, at least what you have done so far does not highlight anything but a mixture of colors that makes it more confusing at least for us who don't really know the subject.


Author Comment

ID: 39870902
Sorry all, description was a little vague...  

To make it easier,  let's get rid of the gray bar and just highlight in yellow...

The data in this report was imported from a text file I created through an Iseries data dump of our manufacturing labor reporting tables.

ORDNO = Mfg Order No.
ORDSTAT = status of order(40 = in process)
OPSEQ = Labor Operation No.
OPDSC = Description of labor step
OPSTAT = status of individual operation (10 = not started, 30 = operation in process, 40 = operation complete.  
The other columns of data are quantities and codes that don't really pertain to this case.

I need to flag operations within each order grouping where labor was not reported. The only way I can tell this is by the sequence of the status of each operation in each order group.  Operations are always completed in sequence.  For example, an order consists of 4 operations (OPSEQ) (0001, 0002, 0003, 0004)  and the status (OPSTAT) = 40 for each operation then I know labor was reported complete for each operation.  If operation numbers 0001 and 0002 are status = 10 and operation 0003 = 40 then I know operation 0003 is complete and labor was not reported for operation 0001 and 0002 when in truth they have been physically done.  I want to highlight the rows 0001 and 0002 to show where labor was not reported.  

the gray highlighting was just to separate each order group as a visual aid but I can work without that...

I hope that helps...  I'm including my macro as well.LaborNotreported-Macro.txt
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

LVL 31

Accepted Solution

gowflow earned 500 total points
ID: 39871618
ok I hope this is what you want.

I kept your shading grey/white per every second item as it makes it easier to read however your yellow shading for selected items has been turned to Red font bold as it makes it easier to locate (that is my own experience) we can change that to cater to your taste.

Pls make sure macros are activated. I copied your sheet to LaborNotReported (2) which removed all shading. Position yourself in that sheet and select from the developer tab Macros then activate the macro Highlight

Check the results and let me know your comments.

Author Closing Comment

ID: 39871839
gowflow,  I am totally floored.  That's exactly what I wanted and I agree the red font is much better.  I wasn't expecting to get a finished macro back but man you saved me huge time on this, especially since programming in excel is not really one of my strengths.  thanks so much for this.  :)  Huge props....
LVL 31

Expert Comment

ID: 39872433
Your welcome and glad I could help out. Pls do not hesitate to post a link in here of any new question you may need help with. I will keep this question monitored.

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 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