0

I have list of item code in text file.

In Excel sheet, there are thousands of item code and related data.

I need to change the color of the background of particular cell of the item code , if that item code is in the text file.

As there are hundreds of item code in text file and thousands of item code and data in Excel sheet, scanning each on text file and updating each on excel sheet will be very time consuming.

Any tips to solve this quickly?

4
  • Would a consideration be to move/copy the contents from the text file and paste into a column in Excel? This way, you won't have to worry about a 'data source' as such.
    – Dave
    Sep 4, 2012 at 9:54
  • that's the good tip. but how can I achieve of searching rapidly and changing the background color?
    – kitokid
    Sep 4, 2012 at 9:58
  • I've tried in the past, and I don't think you can by Cell background color. I won't post an answer although sadly, I'm confident you can't. However, would you consider the forecolor (the colour of the text)?
    – Dave
    Sep 4, 2012 at 10:09
  • forecolor is considerable too. any tip?
    – kitokid
    Sep 4, 2012 at 13:08

1 Answer 1

0

This is not perfect but as close as I can get.

Copy this into D1:

=IF(ISNA(VLOOKUP(A1,$B$1:$B$4, FALSE, FALSE)),"",A1)

and then drag down the entire length to copy the formula for as long as needed. In the example below, I copied it down to D4.

Column A is the value to look up in Column B. Column B is the column of which results to look through (eg, the contents of your text file pasted in). IE. A looks at column B, column B doesn't look at column A (or even know it exists).

enter image description here

Now, if you want to change the colour as well, select the entire D column by clicking it. Then select Conditional Formatting under Styles in the Home tab and create new rule.
Click on Format only cells that contain.
Change it from Cell Value to No Blanks.
Click preview and select the colour you want. Click OK

1
  • Did this work for you?
    – Dave
    Sep 7, 2012 at 12:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .