• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating Conditional Formatting with VBA

J

Jeffrey Lebowski

Member
Hi All,

I have created a materials order tracking log (attached) for a friend. The ‘Orig’ tab is what he gave me, and initially had used colored rows to separate the different systems (column F) which caused him problems when sorting. My solution on the ‘Work’ tab was to conditionally format and color code the rows based on the system. This worked well but I think it can be better. The conditional formatting is set for a fixed range and a fixed number of systems. This poses two potential future problems:

1) Adding a new system – currently each new system would be added to the index page and a CF rule created on the main page.

2) Exceeding the "Applies To" range – the CF rules are all Applied To a fixed range. When the amount of data rows exceeds this range, each CF rule will need to be modified.

I am thinking it might work best to incorporate some VBA. I would really appreciate any suggestions on how to approach this problem.

Thank you!
 

Attachments

  • 000-SubmlLog-REV4.xlsx
    86.5 KB · Views: 6
Hi Jeff ,

I would suggest that you look at what exactly your friend wants to use this for ; using so many colors is a basic visual nightmare , though it may also be a visual basic one.

If the purpose is to interactively look at select items , then the same rules which will be used to color the rows , can be used to extract them or even filter the data in place.

Narayan
 
You're right. My solution to his problem probably went in a direction that was not the best, most efficient and simple solution. So please allow me to take a step back with this explanation:

The first tab of that file is what my friend sent me. He asked me how he could sort by column B ('Sbtl No') while keeping the systems (col F) grouped. He had separated the Systems using physical rows as borders, which were not good for sorting. In other words, the goal is to have System 01 rows sorted smallest to largest by Sbtl No, then all System 02 rows sorted S to L by Sbtl No, etc.

My initial approach was to use a pivot table but I struggled as I am not the biggest fan of pivot tables.

Thank you for your input, any feedback is greatly appreciated!
 
Last edited:
Hi Jeff ,

Looking at the tab labelled Orig , which I assume contains the original data , everything appears to be in sorted order ; of course there are a lot of rows where the cells in column B are blank.

Is it possible for you to upload a file where everything is in its original unsorted order ?

Narayan
 
You must log in or register to reply here.
Back
Top