I am having some difficulty finding where to start with this , i have found a few codes in other forums but am not able to manipulate them to my needs , so here goes my best to explain this -
So to start i have a spreadsheet with multiple sheets , this is a POB (personnel on board) , within this excel file i have a sheet that is a "personnel info" database , it essentially is a spreadsheet that will increase indefinitely with varying personnel's information as the years progress (only when they come on board the vessel) , the database has 2 sets of information that i use in the main sheet (Daily POB) the personnel's company they are associated with and their body and bag weights , i have no issues with the body and bag weight portion, i do however have issues with the company portion , i have a vba coded formula that works like VLOOKUP , it incorporates the background color as well , it works fine (=CLookup(C3,'Personnel Info'!$A$3:$C$100,2)) this is what it looks like when typed in (if the VBA coding is needed i can post it as well)
The main issue with this VBA code is that the A3:C100 (i would actually prefer it up to C1000 so i would not have to update the formula too frequently) slows down the excel file when opening it and people i send this spreadsheet to and others that use it when im not here are starting to complain it takes too long to open, i did some research and found that index/match is being used in place of VLOOKUP these days and could potentially resolve my slow open issue
Is there a way to code in VBA an index/match formula that incorporates the cell color along with the text from one sheet to another ?
***IF MORE INFORMATION IS NEEDED I WILL DO MY BEST TO PROVIDE IT***
So to start i have a spreadsheet with multiple sheets , this is a POB (personnel on board) , within this excel file i have a sheet that is a "personnel info" database , it essentially is a spreadsheet that will increase indefinitely with varying personnel's information as the years progress (only when they come on board the vessel) , the database has 2 sets of information that i use in the main sheet (Daily POB) the personnel's company they are associated with and their body and bag weights , i have no issues with the body and bag weight portion, i do however have issues with the company portion , i have a vba coded formula that works like VLOOKUP , it incorporates the background color as well , it works fine (=CLookup(C3,'Personnel Info'!$A$3:$C$100,2)) this is what it looks like when typed in (if the VBA coding is needed i can post it as well)
The main issue with this VBA code is that the A3:C100 (i would actually prefer it up to C1000 so i would not have to update the formula too frequently) slows down the excel file when opening it and people i send this spreadsheet to and others that use it when im not here are starting to complain it takes too long to open, i did some research and found that index/match is being used in place of VLOOKUP these days and could potentially resolve my slow open issue
Is there a way to code in VBA an index/match formula that incorporates the cell color along with the text from one sheet to another ?
***IF MORE INFORMATION IS NEEDED I WILL DO MY BEST TO PROVIDE IT***
