Auto set value/formula in entire column cell

[Derrick]

Hello,

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.



Thanks for the anticipated help.

Regards,
Derrick

 

[Bob Bridges]

Whew! That first paragraph is a lot of words for what I hope will turn out
to be simpler than it looks. 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]

Hi Bob,

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.

Thanks and regards,
Derrick

 

[Bob Bridges]

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]

Hi Bob,

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

Regards,
Derrick

 

[Bob Bridges]

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]

Hi Bob,

You are right on the money

I love it when my thoughts can be understood.

That is exactly what i am looking for.

Best regards,
Derrick

 

[Derrick]

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]

Oh, I won't just write the function for you. Partly that's because I think
like a teacher and I don't want you to get out of this without learning
something. But mostly it's because if you didn't write the routine yourself,
then you don't know how to fix it if it isn't quite right, or when you want
to make it a little smarter a month from now. But if you wrote the original
code, you won't find it hard to add the new routine.

You want to switch to email, though? Easier than going back and forth in
this. If so, contact me at (e-mail address removed).