Macro Question

[goofy78270]

Just as a warning, I am looking to do something weird. I currently have a worksheet that I track pricing changes in. From month to month, I highlight changes for different products based on the investor within a specific color so a second party can ensure that all changes made are correct..

What I would like to do is to have the sheet look at the first adjustment within the list and copy the same values down for each adjustment after that. This isnt so bad seeing as how I can simply say the such and such = the first cell.

The tricky part comes in that I would first like to have the formats copied down to all the following adjustments along with the values.
The second tricky part comes with if the first product is deleted due to whatever reason, I would like to account for this and have the sheet set the next in line as the top and have all others follow it.

I hope that this makes sense. If not, maybe I can upload a workbook for you to see.

 

[erik.van.geit]

I'm not following you here.
The purpose of the code was not to "copy" cells, but just to color the backgrounds.

 

[goofy78270]

Correct, now, I am looking to copy the changed values which occur within Columns B:F for each Empower ID.

Sorry this was not stated in the beginning.

 

[erik.van.geit]

refering to your first example:

Find Empower IDs listed on "sheet1" in sheet "Price Adjustments"
If the specifications are changed put that row on "sheet2". Is that the question? If not please provide another example.

beste regards,
Erik

 

[goofy78270]

Yes, that is what I am looking to do along with the color copy for the cell.

 

[erik.van.geit]

Oh, my expression was confusing.

If the specifications are changed

I meant: if the input is different from the data on sheet "Price Adjustments".

example of what I meant:
  B          C                D                                    
1 Empower ID Rate Sheet Order Description                          
2 200        1                Escrow Waiver                        
3 27         2                LTV 65.01-95.00% & CLTV 90.01-95.00% 
4 23         1                40 Year Amortization                 

Price Adjustments
[Table-It] version 07 by Erik Van Geit

  A          B           
1 Empower ID Description 
2 23         abcde       

Sheet1
[Table-It] version 07 by Erik Van Geit

On "sheet1" the data for EmpowerID 23 are "abcde", but on the sheet "Price Adjustments" it is "40 Year Amortization". So 23 would appear in the new list.

But apparently there are no differences in your example. So you probably want to copy the colored lines to the other sheet?

 

[goofy78270]

Given your example, with the change in description to EMPOWER ID 23, sheet 1 should now read

A B C
23 1 abcde

and also color coded tro whatever color is within sheet two.

The purpose of this is to simplify the changes made to an adjustment by simply changing everything in one place and having sheet one updated via a command button running this macro.

 

[erik.van.geit]

I'm confused. Looks better to me that you provided another example.
The case will be solved rather easily I think.

 

[goofy78270]

Table 1 - before

A B C D
1 Product Empower ID Rate Sheet Order Description
2 C00 200 1 Escrow Waiver
3 C00 27 2 LTV 65.01-95.00% & CLTV 90.01-95.00%
4 C01 23 1 40 Year Amortization
5 C02 200 1 Escrow Waiver
6 C02 125 2 Investment Property & LTV <=75.00%
7 C02 133 3 Investment Property & LTV 75.01-80.00%
8 C02 141 4 Investment Property & LTV 80.01-90.00%
9 C02 27 5 LTV 65.01-95.00 &CLTV90.01-95-C02/C08/C18
10 C02 35 6 LTV 70.01-80.00% & Cash Out Refi-C02/C18 only
11 C02 49 7 LTV 80.01-85.00% & Cash Out Refi-C02/C18 only
12 C02 59 8 LTV 85.01-90.00% & Cash Out Refi-C02/C18 only


Table 2 - Adjustments
A B
1 Empower ID Description
2 23 40 Year Amortization
3 27 LTV 65.01-95.00% & CLTV 90.01-95.00%
4 35 LTV 70.01-80.00% & Cash Out Refi-C02/C18 only
5 49 LTV 80.01-85.00% & Cash Out Refi-C02/C18 only
6 59 LTV 85.01-90.00% & Cash Out Refi-C02/C18 only
7 125 Investment Property & LTV <=75.00%
8 133 Investment Property & LTV 75.01-80.00%
9 141 Investment Property & LTV 80.01-90.00%
10 200 xyz



Table 3 - After
A B C D
1 Product Empower ID Rate Sheet Order Description
2 C00 200 1 xyz
3 C00 27 2 LTV 65.01-95.00% & CLTV 90.01-95.00%
4 C01 23 1 40 Year Amortization
5 C02 200 1 xyz
6 C02 125 2 Investment Property & LTV <=75.00%
7 C02 133 3 Investment Property & LTV 75.01-80.00%
8 C02 141 4 Investment Property & LTV 80.01-90.00%
9 C02 27 5 LTV 65.01-95.00 &CLTV90.01-95-C02/C08/C18
10 C02 35 6 LTV 70.01-80.00% & Cash Out Refi-C02/C18 only
11 C02 49 7 LTV 80.01-85.00% & Cash Out Refi-C02/C18 only
12 C02 59 8 LTV 85.01-90.00% & Cash Out Refi-C02/C18 only

How is this for just a quick response?

 

[erik.van.geit]

Hi,

Please put this in a format which can be copied to Excel, without "massive" manipulations. I still think it will be solved rather easily, but you will understand that spending time to get the correct formats is a waste.
Next 3 days almost no time, but be sure I'll come back.

best regards,
Erik

 

[goofy78270]

Here are the new tables. Hope this helps.

Also, when I run the previous code for the first time on a sheet, everything gets highlighted with whatever the last color in sheet two is, but once I run it again, everything is okay. Just a little weird.

  A   B    C                                                            D  E  F  G                
2 C00 9    Conforming Loan Limit Exceeded-Agency Guidelines                        
3 C01 9    Conforming Loan Limit Exceeded-Agency Guidelines                        
4 C02 9    Conforming Loan Limit Exceeded-Agency Guidelines                        
5 C03 9    Conforming Loan Limit Exceeded-Agency Guidelines                        
6 C04 9    Conforming Loan Limit Exceeded-Agency Guidelines                        
7 C06 1502 Full Doc/Std Inc/Verif Ast PR - Min LA: $10,000, Max LA: $2M                           
8 C06 1503 Full Doc Second Home - Min LA: $10,000, Max LA: $2M                                    
9 C06 1507 Std Inc/Verif Asst Second Home - Min $10K, Max $1.5M                                   

Table 1
[Table-It] version 06 by Erik Van Geit

   A    B                                                            C  D  E  F        
 2 2    VA Conforming Loan Limit Exceeded-Guidelines                                   
 3 4    VA Loan Limit Exceeded                                                         
 4 6    Conforming Loan Limit Exceeded-CW Guidelines                                   
 5 7    Conforming Loan Limit Exceeded-Regions Guidelines                              
 6 8    Conforming Loan Limit Exceeded-Wells Fargo Guidelines                          
 7 9    abcd                                                                  This is changing 
 8 1502 Full Doc/Std Inc/Verif Ast PR - Min LA: $10,000, Max LA: $2M                   
 9 1503 Full Doc Second Home - Min LA: $10,000, Max LA: $2M                            
10 1507 Std Inc/Verif Asst Second Home - Min $10K, Max $1.5M                           

Table 2
[Table-It] version 06 by Erik Van Geit


  A   B    C                                                            D  E  F  G                
2 C00 9    abcd                      This is changing 
3 C01 9    abcd                      This is changing 
4 C02 9    abcd                      This is changing 
5 C03 9    abcd                      This is changing 
6 C04 9    abcd                      This is changing 
7 C06 1502 Full Doc/Std Inc/Verif Ast PR - Min LA: $10,000, Max LA: $2M                           
8 C06 1503 Full Doc Second Home - Min LA: $10,000, Max LA: $2M                                    
9 C06 1507 Std Inc/Verif Asst Second Home - Min $10K, Max $1.5M                                   

Table 1 revised
[Table-It] version 06 by Erik Van Geit