Assistance Needed for Alternative to VLOOKUP Cell Formating

[nchambless]

I know that I have read all over that you cannot carry over the format of a cell with a VLOOKUP. (Font/ Cell Color, etc) I need help with an alternative. I am somewhat of a beginner but usually catch on quickly. This is what I am working with:

I have a worksheet that have multiple sheets- I have named them by the weeks of the year. When I export into excel I run a macro I created to format the sheet to clean the report to only the data I need and am able to review the report for that week. I have created a second macro that has a VLOOKUP that runs in it that pulls data from columns K, L, M, and N based if column B is the match. (I am sorry that my verbiage is not correct/ ideal.) Hopefully you understand. The VLOOKUP pulls that data and fills it to the corresponding fields I have placed the formula, and works properly.

However, In this second macro I have the VLOOKUP pulling along with a Function I created so that it will pull the data in those columns (K,L,M, and N) from the sheet prior. -I have created this function so that the macro does not run based off a specific named sheet but always by going 1 sheet back. The point of this is to pull my Notes/ Status Updates/ Follow Up Dates from the prior week (Prior Sheet) forward so that I am not duplicating work every work as I will be reviewing thousands of lines once I have this second macro fine tuned with this.

The issue: The VLOOKUP doesn't pull formatting of the cells. What are alternative options I have so that I can differentiate the text/ data/ overall information that was pulled forward from the previous week. Can I have that text colored red? Can add a small column added that I can add a formula or something that will flag if it was pulled from the VLOOKUP? I would like to go as simple as possible to keep the report looking clean but need suggestions. Please Help!


Thank you in advance!

- A hopeful soon-to-be Excel Intermediate user!

 

[oldbrewer]

So are you trying to compare this week with last week ? If so I am thinking a master sheet that pulls info from the CURRENT sheet and also the same information from the previous sheet. How many cells need to be compared eg it might just be sales and profits....

 

[nchambless]

I am trying to compare Last week to this week- so technically we view last week (the prior sheet) to be the master because it is the most current. What I didn't explain before is that this report is items that need an "action" of some sort within what I do in my role. This is not a financial report. It is a report to track progress of part numbers and Late items. The fields on the prior tab that I have the VLOOKUP pull into the new sheet (the new week) are our status updates and follow up dates, etc. I wanted the information from the specified columns on the prior week to pull forward if that item was still reporting so that I knew what the most current status was as of last week/ where I left off with it last.

For example if the status explained that the item was received into Chile but we were waiting on customs approval for it to be received into inventory, there is no action needed on the item and I do not want to research the item again. The VLOOKUP works properly and pulls the details from the prior week and I know right away it already arrived and I am just waiting. However, I want to color the font, or differentiate the text/ data that is pulling over from the VLOOKUP so that I can know when it was pulled over from the VLOOKUP vs. when it was typed in manually as an update.

The VLOOKUP is working properly, I just want to know how I can format or flag the cells that are pulling from the VLOOKUP.

 

[oldbrewer]

to really understand this, if in A1 you have =B1 you want A1 to be red because it contains a formula - but when you type 99 into A1 you want the red to disappear ?

 

[nchambless]

Yes!

 

[oldbrewer]

put a formula in A1
highlight A1
format, conditional formatting
formula
=isformula(indirect("rc",false))

it doesn't work for me but I only have excel 2000