Coloring cells in database corresponding to a smaller list

sjk

New Member
Joined
Apr 8, 2002
Messages
14
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Apr 8, 2002
Messages
14
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDE
1MainDatabaseMainDatabase
21Asset1Asset4
32Asset2Asset9
43Asset3Asset13
54Asset4
65Asset5
76Asset6Simulationwithasampledatabase
87Asset7
98Asset81.SelectMainDatabaseentriesB1:B16assourcedata
109Asset92.UseE1:E4(KeyAssetDatabase)entriesascriteria
1110Asset103.UsingAdvancedFilter,highliteKeyAssetsintheMainDatabase
1211Asset114.Colorthebackgroundofthefilteredrecords(sayyellow)
1312Asset125.DATA|FILTER|ShowAll
1413Asset13
1514Asset14viewtheKeyAssetshighlitedintheMainDatabase
1615Asset15
Sheet7
</SPAN>

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

Regards!

Yogi
 

sjk

New Member
Joined
Apr 8, 2002
Messages
14
Thanks, Yogi. I think that will do it for me - and very easy. SJK.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top