The full data import is about 2000 lines and 100 columns. Worksheet A is a
summary of some of that data in a more management style. ;-)

I have now copied to a distant part of Worksheet A the status of each task
so it is on the same line as its corresponding ID. The range is currently
15x300 cells and although I could set conditional formatting to pick up the
word Complete and change the cell, I cannot think of a way of copying/pasting
the conditional format only. There is existing formatting on these cells that
cannot be overwritten and the thought of manually doing each cell's
conditional format fills me with dread!

I have therefore tried to do the following:

Dim r As Range
Dim c As Range

LastRow = Cells(Rows.Count, "AK").End(xlUp).Row
Set r = Range("CG14:CU" & LastRow)

For Each c in r
If c.Value = "Complete" Then
Cells(c.Row,c.Column - 30).Interior.ColorIndex = 5
Cells(c.Row,c.Column - 30).Font.ColorIndex - 2
End If
Next

But I get error 13 Type Mismatch on the If c.Value = "Complete" Then line.
Hovering over it, I see another error 2402, which I cannot find in help and a
quick google search implies that the value cannot be found.

Any suggestions as ever gratefully received!

Louise

"Rick Rothstein (MVP - VB)" wrote:

Can you not delete the worksheet and simply delete the excess data on it
instead (leaving the task data in place until the next import when
everything would again be overwritten)? That way the named cell would always
remain in place from import to import and the only data you would have to
'tolerate' remaining around would be just the task data. Or is this task
data the huge part of the worksheet?

Rick


"Ladymuck" wrote in message
...
Thanks for the suggestion Rick. I have considered using named ranges in
conditional formatting but, unfortunately, Worksheet B is automatically
deleted at the end of the macro as it is huge and contains more data than
is
needed for the report. It's simply imported, used as source data to run
several calculations and then removed. If I was retaining it then what you
have suggested would be ace.

"Rick Rothstein (MVP - VB)" wrote:

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