Coloring cells in database corresponding to a smaller list

[sjk]

I have a database of several thousand assets, each with a unique number. From a much smaller list of, say, 50 assets, each with a number, (below the main data), I would like to be able to color the cells in the main database corresponding to these 50 assets, thus marking the main database. However, and this may be tricky, I would like to be able to either STOP this process or message me if the asset in the main database has already been marked (cell colored). I would assume that VBA would have to be used for this?? Thanks in advance, SJK.

 

[PaddyD]

do you mean that one of the 50 asset items in the smaller list might turn up more than once in the larger, & you only want to highlight the first occurance?

paddy

 

[sjk]

Actually, I should have clarified that: the main database has only unique items. The smaller group of 50 will be marked in the main database, but then next week, I could have another group of, say, 23 to be marked. I will repeatedly have small groups to be marked (cells colored) in the main database. If the main database is already marked, I don't want the cell colored again, if in the smaller group an erroneous repeat number comes up. (I realize that one could easily check for duplicates in the subgroups - but I want to make it as "idiot proof" as possible.) Does this make sense? Thanks!

 

[Yogi Anand]

On 2002-10-02 21:29, sjk wrote:
I have a database of several thousand assets, each with a unique number. From a much smaller list of, say, 50 assets, each with a number, (below the main data), I would like to be able to color the cells in the main database corresponding to these 50 assets, thus marking the main database. However, and this may be tricky, I would like to be able to either STOP this process or message me if the asset in the main database has already been marked (cell colored). I would assume that VBA would have to be used for this?? Thanks in advance, SJK.

Hi sjk:

I have used Advanced Filter to select the Key Assets from the Main Database and then colored the background of the Key Assets in the Main Database. See the worksheet simulation ...

y021002.xls
	A	B	C	D	E
1		MainDatabase			MainDatabase
2	1	Asset1			Asset4
3	2	Asset2			Asset9
4	3	Asset3			Asset13
5	4	Asset4
6	5	Asset5
7	6	Asset6			Simulationwithasampledatabase
8	7	Asset7
9	8	Asset8			1.SelectMainDatabaseentriesB1:B16assourcedata
10	9	Asset9			2.UseE1:E4(KeyAssetDatabase)entriesascriteria
11	10	Asset10			3.UsingAdvancedFilter,highliteKeyAssetsintheMainDatabase
12	11	Asset11			4.Colorthebackgroundofthefilteredrecords(sayyellow)
13	12	Asset12			5.DATA|FILTER|ShowAll
14	13	Asset13
15	14	Asset14			viewtheKeyAssetshighlitedintheMainDatabase
16	15	Asset15
Sheet7

</SPAN>

I hope this gives you some food for thought toward your final solution.

Regards!

Yogi

 

[sjk]

Thanks, Yogi. I think that will do it for me - and very easy. SJK.