Vlookup with cell background color

[Shaikh Aziz]

Dear All,

Please help me out or suggest me how to vlookup with cell backgrond color, eg. i have sheet1 & 1000 names in column A and i want to vlookup columnB values with multiple cell colors (with formatting) in sheet2 ColumnB according to columnA. please suggest me any shortcut or vlookup formula for V-looking up with cell background colors.

NOTE: I have matched names in Sheet1 & Sheet2 in ColumnA. and we want to vlookup along with cell background color from one sheet to another sheet.

Also PFB cross posting link for reference, i am still haven't found an answer, please help me.

Vlookup with cell background color

Dear All, Please help me out or suggest me how to vlookup with cell backgrond color, eg. i have sheet1 & 1000 names in column A and i want to vlookup columnB values with multiple cell colors (with formatting) in sheet2 ColumnB according to columnA. please suggest me any shortcut or vlookup...

www.excelforum.com

Thankyou All.

 

[Zot]

I believe the Excel 2007 still can use old Excel4 function GET.CELL. There are many parameters to extract info from a cell. You can refer to this. There are limitaion like you need to recalculate if color changed for example.

Info only - get.cell arguments

Get.cell (an old xl 4 macro function) can be used to return more info about the worksheet environment than is available with the cell() function. No VBA code (or skills) are required. One complication is that you cannot use get.cell directly in the worksheet. However, there is a...

www.mrexcel.com

I think the limit is also that it works for color within Excel Color Index, not to many other variance that can be defined by RGB color.

Anyway, if you use VLOOKUP, then it will stop at first instance match. Unless you only have only unique color in range, then you will have problem. Another option is using VBA to loop whole range and get color and also value.

Do you want to match color and extract value or both?

 

[Shaikh Aziz]

Dear Zot,

Thankyou for your revert, I will try Get.Cell option as given above links.

and I do not want to match color I just want to extract value along with background color.

Please find attached example screenshot, In sheet1 & sheet2 we have same name in ColumnA. we have to vlookup values of ColumnB in sheet2 in ColumnB but along with cell background color.

NOTE: there is 1000+ rows in columns with 5 to 6 multiple colors.

 

[Zot]

I think I get confused here. You are not getting the color value but to look for value and copy everything. You can just run VBA and use normal copy paste, right? I thought you want to get the conditional color code.

 

[Fluff]

Is the cell colour being set manually or by conditional formatting?

 

[Shaikh Aziz]

Is the cell colour being set manually or by conditional formatting?

Hey fluff, Hii,

cell colour being set manually.

 

[Fluff]

In that case have a look here How to copy source formatting of the lookup cell when using Vlookup in Excel?

 

[Shaikh Aziz]

I tried this fluff, but it's only working in same sheet, we want to vlookup (with cell color) from one worksheet to another worksheet.

 

[Fluff]

I've just pulled you file from EF & see that you say you have almost 50,000 rows of data.
Any form of VBA is going to be slow, especially if it has to work on change events. Added to which there is no-way a macro knows if you have changed the colour of a cell.
The only realistic way would be to use a macro that you run from time to time.

 

[Shaikh Aziz]

Okay, I will check what alternate option we can do.

and thanks for your revert.