Help Building a complex Macro

**shane3200** · 03-30-2017, 10:14 AM

Hey guys, I used this forum to help build this excel sheet for the company that I work for. It has been amazing for what we were trying to get out of the spreadsheet. But I want to try and enhance it by building a macro for the steps that are listed on the worksheet. I am not entirely sure how it would work or how to build it and was hoping you all could figure the problem out. With the data looking like the "SPREADSHEET" worksheet, I want the macro to format each line of data separately in the box shown in the "Template" Worksheet. There will probably be anywhere from 200-350 separate lines of data so the entire "TEMPLATE" worksheet should be 2400-4200 lines long as each box is 12 lines long. As for the colored cells separating the boxes, I believe that part will be done manually outside of the macro because the color should be different every 10-20 boxes depending on which NBHD the data correlates with. The excel book is attached. Thanks!!

**k64** · 03-30-2017, 02:46 PM

Are you looking for something as simple as this?

Please Login or Register  to view this content.

**6StringJazzer** · 03-30-2017, 03:09 PM

Maybe I'm not clear on what you're trying to do. You could preformat the entire template first and then install the conditional formatting rule that you are telling people to put in themselves. I don't see anything in those steps that can't be built into the template.

Also you don't need to enter Developer > Design Mode to do any of those steps.

**shane3200** · 04-04-2017, 11:44 AM

"Maybe I'm not clear on what you're trying to do. You could preformat the entire template first and then install the conditional formatting rule that you are telling people to put in themselves. I don't see anything in those steps that can't be built into the template.

Also you don't need to enter Developer > Design Mode to do any of those steps. "

REPLY -

Each person that uses this template will have a different number of sales in a neighborhood group which is where the conditional formatting comes in. For example, if a person has 12 sales in a group then 12 boxes will end with a blue row and then there next group of 7 sales will end with pinks rows each. So I think the conditional formatting will have to be manual. Unless I could add a field in the spreadsheet that correlates with the group that the sale should be in and then have the conditional formatting set to read from that field. The above visual basic code worked but it did not bring in the picture inserts and the colored rows.

**shane3200** · 04-04-2017, 11:46 AM

Looks a lot more simple than I expected. But the code only brought in the data and field names correctly, it failed to bring in the colored rows as well as the picture inserts. Thanks!

**shane3200** · 04-04-2017, 12:00 PM

The attached excel book is an example of what I want the finished product to look like. If there is a way I could code a conditional formatting into the macro that will change the color under each box that correlates with a specific group, in which a "group field" would be added to the spreadsheet.

Thanks!