Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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 Rook Sep 4 '12 at 9:54
    
that's the good tip. but how can I achieve of searching rapidly and changing the background color? –  kitokid Sep 4 '12 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 Rook Sep 4 '12 at 10:09
    
forecolor is considerable too. any tip? –  kitokid Sep 4 '12 at 13:08
add comment

1 Answer

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

share|improve this answer
    
Did this work for you? –  Dave Rook Sep 7 '12 at 12:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.