I hope I can explain this ok!

Worksheet A is laid out as:

ID Task1 Task2 Task3 Task4
1 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
2 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
3 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy

etc

Worksheet B is laid out as:
ID Task1 Task2 Task3 Task4
1 complete on hold not started not started
2 complete complete complete not started
3 not started not started not started not started

etc

Not everything on Worksheet B can be found on Worksheet A and vice versa

What I need to do:
Is the ID on A found in B?
If yes, look along the row and where something is 'complete' on B, set the
corresponding cell on A as Blue interior, White text.
Otherwise, do nothing

In the example above, cells B2, B3, C3, and D3, would all be Blue/White. The
cell contents of A cannot be overwritten. Worksheet B is deleted once the
macro has finished running as it is just a temporary data store for doing
other background functions.

I have Chip Pearson's (thanks Chip!) fabulous modColorFunctions installed as
I've used it elsewhere in this workbook if that is of use here.

Thanks in advance for any help, all comments gratefully received.

Louise

You don't need to use a macro to do what you asked, you can do it using
Conditional Formatting. Go to "Worksheet B" and select Cell B2, click in the
Name Box (that is the edit field on the formula bar to the left of the
formula fill-in field) and type in StartCell for its name (you can use any
name you want, but if you change it from this, you will have to change the
conditional formula below to match). Okay, now go back to "Worksheet A" and,
starting in Cell B2, select all your potential Task columns for as many row
down as you think you may ever have ID numbers entered (that is, don't be
afraid to include blank cells in the range). With that range still selected,
click on Format/Conditional Formatting on the Excel menu bar; select Formula
Is from the first drop-down and put this formula in the 2nd field...

=ISNUMBER(MATCH("Complete",OFFSET(StartCell,ROW(B2 )-2,COLUMN(B2)-2),0))

Next, click the Format button and choose White for the color of your text on
the Font tab and the Blue color you want for the interior of the cells on
the Patterns tab. Now, OK your way back to the worksheet and the appropriate
cells should be highlighted.

Rick


"Ladymuck" wrote in message
...
I hope I can explain this ok!

Worksheet A is laid out as:

ID Task1 Task2 Task3 Task4
1 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
2 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
3 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy

etc

Worksheet B is laid out as:
ID Task1 Task2 Task3 Task4
1 complete on hold not started not started
2 complete complete complete not started
3 not started not started not started not started

etc

Not everything on Worksheet B can be found on Worksheet A and vice versa

What I need to do:
Is the ID on A found in B?
If yes, look along the row and where something is 'complete' on B, set the
corresponding cell on A as Blue interior, White text.
Otherwise, do nothing

In the example above, cells B2, B3, C3, and D3, would all be Blue/White.
The
cell contents of A cannot be overwritten. Worksheet B is deleted once the
macro has finished running as it is just a temporary data store for doing
other background functions.

I have Chip Pearson's (thanks Chip!) fabulous modColorFunctions installed
as
I've used it elsewhere in this workbook if that is of use here.

Thanks in advance for any help, all comments gratefully received.

Louise

I have been working on somthing similar to what your doing, maybe this will
get you started.

Sub Compare()
Dim foundcell As Range
With Worksheets(2).Range("A1:A65536")

Do
On Error Resume Next
Set foundcell = .Find(What:=ActiveCell.Value, lookAt:=xlWhole,
LookIn:=xlValues, SearchOrder:=xlRows, MatchCase:=True, MatchByte:=True)

If Not foundcell Is Nothing Then
ActiveCell.Offset(0, 2).Value = "Found " & ActiveCell.Value & " in "
& "Sheet2 " & foundcell.Address
ActiveCell.Offset(0, 2).Interior.ColorIndex = 41
ActiveCell.Offset(0, 2).Font.ColorIndex = 2
ElseIf foundcell Is Nothing Then
ActiveCell.Offset(0, 2).Value = "Didn't Find " & ActiveCell.Value &
" in " & "Sheet2 "
ActiveCell.Offset(0, 2).Interior.ColorIndex = 3
ActiveCell.Offset(0, 2).Font.ColorIndex = 2
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
End With
End Sub


"Ladymuck" wrote:

I hope I can explain this ok!

Worksheet A is laid out as:

ID Task1 Task2 Task3 Task4
1 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
2 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy
3 dd/mm/yy dd/mm/yy dd/mm/yy dd/mm/yy

etc

Worksheet B is laid out as:
ID Task1 Task2 Task3 Task4
1 complete on hold not started not started
2 complete complete complete not started
3 not started not started not started not started

etc

Not everything on Worksheet B can be found on Worksheet A and vice versa

What I need to do:
Is the ID on A found in B?
If yes, look along the row and where something is 'complete' on B, set the
corresponding cell on A as Blue interior, White text.
Otherwise, do nothing

In the example above, cells B2, B3, C3, and D3, would all be Blue/White. The
cell contents of A cannot be overwritten. Worksheet B is deleted once the
macro has finished running as it is just a temporary data store for doing
other background functions.

I have Chip Pearson's (thanks Chip!) fabulous modColorFunctions installed as
I've used it elsewhere in this workbook if that is of use here.

Thanks in advance for any help, all comments gratefully received.

Louise