Well, a VLOOKUP can look up a value in a table and bring back...well, look at
it like tthis: Say I have a worksheet with a list of village names in column
A, and then some data on each village - say X coordinate, Y coordinate,
distance (from a central point), the owner, and the owner's alliance - in
columns B through F. We'll call that my "map" and I'll store it in a sheet
called m6. Now in another sheet named "Dispatch" I have a few village names
that I might send reinforcements to and I want to look up their locations
automatically; if the village name on this new sheet is in column A and I
want to put the X and Y coordinates in columns K and L, then in K2 I'd put
=VLOOKUP(A2,m6!A:F,2,0). That says "get the value in A2 (the village name
for this row) and look it up on the map sheet, column A; when you find the
row, look to the right and fetch me the value from the next column over".
That's the X coordinate; to get the Y coordinate I set L2 equal to
=VLOOKUP(A2,m6!A:F,3,0). Then I copy K2:L2 down the rows so that the
coordinates are automatically looked up for each village.

Now, if I understand you, your situation is different: The table you're
looking in (you're calling it a "name list") has rows of the right sort, but
once you find the row you don't have a particular column you want to pull
from. Instead, you want to look out along that row checking the color of
each cell, and when you find one of the right color you want to copy THAT
value back into column B of your original sheet, and change the color in the
name-list cell so that you and your program will both know that datum is now
"taken". Am I getting warm?

--- "Derrick" wrote:
I am desperate so trying to be as explicit as possible ;-)

Available data is just my term (see the F.Y.I). The code will then continue
after matching X to look up a column 1 in a named list for cells with no rad
color and use the next data in that white uncolored cell and then insert that
data in the target cell and then color that cell in the named list red or
orange.

I am not sure if a vlookup would do this because the trigger for the data is
the color of the cell and i am not good enough to give this a thrashing.

--- "Bob Bridges" wrote:
...It sounds like you want your code to compare
each cell in B to some value X. Then somehow there's search for "available"
data in another table, but I don't know what "available data" means. The
result of the search is put into another cell on the same row and colored;
that part is easy enough. But what's the nature of this search? I imagine
it as the equivalent of a VLOOKUP, where you look for some value Y in a table
and, if it's found, return the contents of a cell Z columns to the right of
it. But where are you getting Y?

--- "Derrick" wrote:
I am looking for a code to add to my existing code that can enable me do a
comparison of data in the cells of column B and match against a value which
will initiate a search for available data in a name list and place this data
into the respective cell on that row then finally paint the cell (in the name
list) as red(used)/orange(reserved)/Green(Not used) to indicate non
availability.

The colors will be the test case for data availability.

My existing code is based on a change event and works ok, i only need to
insert the code where there is a selection of data from a list (validation
list) and input the right data into relevant cell5

For Each cell In Target.Offset(0, 0)
cell.Offset(0, 14).Formula = "=""ULUS"" & O:O"
'this is where i want to place the script
cell.Offset(0, 5).Formula = "test condition"

F.Y.I
My named list has 7 colums of info with relevant header, data1, data 2
....data7 so the search on the list i would think would be referencing
relevant header then looking for data availability (white cell) and doing its
magic to use up and color the cell.

And there are multiple name list for each test case. because the change
event has currently 4 cases within the select case. so i can adapt the code
to each test case.