• 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.

sorting cells and their borders

G

greg.begin

Member
I have a file that has a list of items. These items have a schedule. the schedule is conditionally formatted to look like a Gantt chart. This file then gets marked with a border style of when work was started and finished. If the work started a macro will put a diagonal border through the cell. If it is finished then the vba will put a diagonal going top to bottom and bottom to top creating an "X". this info then gets updated to MS project. a planner then changes the order of the Gantt to keep a waterfall look on the items. i then need to re-sort the items in the new order. when i do this, my borders do not follow and creates a big mess. any thoughts on how to move the border formats with the sort?
 
Are the border beings applied to cells with formulas or constants?

When you sort the data, are you using a regular sort or is it a macro?

To make things easier to sort/read data, would it be possible that the macro that currently just applies cell formatting also puts some word/number in a helper column that we could later look for?
 
borders are being applied to constants. for example, if work started the value is a 1, this gives me an actual start date, if the work is finished it is a 2, this gives me an actual finished date. also, when work is being performed, they click on "Working" on thier userform and it will put the same border format as the start to show that work is being done.


i am sorting based on a recorded macro sorting. i tried conditionally formatting, however, it won't let me do diagonal formatting of borders.
 
Hi Greg ,


I am not clear on the problem , can you please clarify ?


1. The cell formatting is being done by VBA.


2. The order of items is changed by a planner , in MS Project.


3. You need to change the order of items in the Excel file , to correspond to this new order.


4. When you change this order , the data and the cell formats no longer correspond.


Is this correct so far ?


If so , my doubt is , why cannot the process in step 1 be repeated ? After all , the original formatting of the Excel file was done by VBA , based on the data within each cell ; if this data has not changed , re-executing the VBA on this same data , should result in the correct formatting.


Am I missing something ?


Narayan
 
yes this is correct. I tried doing a sort based off of the new order from project, but the borders do not follow any type of sort. the challenge is the gantt part of the file. the visual gantt borders wont sort with the items (new order). i did write code before to go to the new order take the first value, find the value on the Gantt copy the entire row and paste it on to a dummy sheet, then proceed to the next item of the new order and find it on the old one and repaste it in the "new order". this however, takes forever.
 
You must log in or register to reply here.
Back
Top