Hi Bob,

Also, i am a novice to VBA just trying to get a spreadsheet workable. It
would be great if the suggestion can accomplish the whole routine :-)

Thanks,
Derrick

"Bob Bridges" wrote:

Got it...or at least I'm getting it. Ok, so ideally your code should call a
function like this:

For Each cell In Target.Offset(0, 0)
cell.Offset(0, 14).Formula = "=""ULUS"" & O:O"
cell.Offset(0, 5).Value = NextAvlName() 'new line in your code

NextAvlName would a) look down the namelist column checking colors, b) pick
out the next available datum (as determined by the cell background color), c)
change the color of that cell to indicate either "used" or "reserved" and d)
return the value to your routine, which stores it in cell.Offset(0,5). Will
that do the trick for you?

And what you're not sure how to do is check the color?

--- "Derrick" wrote:
I am not look along the row but down a column withing the named list taking
the first value in cell without any coloring.

--- "Derrick" wrote:
....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.

--- "Derrick" wrote:
....search for available data in a name list and place this data...then
finally paint the cell (in the name list) as red (used) / orange
(reserved) / Green (Not used) to indicate non availability.

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.