On Mar 22, 10:40 am, "Guerilla" wrote:
Thanks for the help. I am still a bit confused however.

The spreadsheet will be layed out in a table fasion. the rows will
contain the person's name and their 5 choics (6 cells in total).
There will be about 40 names in a list. I need to programe to look at
the first person and their first choice, check to see if their choice
is available and then allocate them to it (perhaps by highlighting
their choice in green. If there 1st chioce is not available their
second choice would be considered, this would be in the same row but 1
column to the right. Once a person has been allocated to a job, their
other choices become redundant, so the program should move to the next
person (one column below the original). As mentioned before, if a job
has 10 places, the check would need to see if the job has been fullt
allocated. Anyone who has not managed to secure a job would need
their name highligted in red.

I hope I have explained that well enough. I could email a simple copy
of the file to anyone who may be able to help.

kind regards,
Matt

Matt,

You have the code in the nested For loop. Simply set up an If...Then
Statement or a Case Statement to evaluate your criteria. Search VBE
Help for "If...Then Statement," "Select Case Statement," and "GoTo
Method" and you should see some helpful information.

The outer loop moves through the row, and the inner loop moves through
the column

For r = 1 To 40 'the row number of the employee
For c = 2 To 6 'the column number of the column value
If (Cells(r,c).Value = (some criteria)) Then
'code
End If

If (allocated) Then
GoTo Allocated
End If

If Cells(r,c).Value = "" Then 'tests for a blank cell
Exit For 'this will exit the column loop
End If
Next

Allocated:
Next

You can test a blank cell with IsEmpty(range/value) or with something
like Cells(r,c).Value = "". As for colors, use the macro recorder or
search "ColorIndex Property/ColorIndex Property as it applies to the
Interior object" in Excel VBA. For example, you can code something
like this: Range("A1").Interior.ColorIndex = 3.

Matt