Bob,

Yes, this seems to be doing exactly what I wanted, so thanks again for your
help.

Chris

"Bob Phillips" wrote:

And that is working fine is it?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ChrisO" wrote in message
...
Mnay thanks Bob. Here is the final version I am now using:

Sub colourcells()
Dim cell As Range
Dim rng As Range
For Each cell In Worksheets("Details").Range("J3:V74")
If Not cell.Value = "" Then 'Ignore Blank Cells
If cell.Value = "NAVL" Then 'Not Available is Grey
Cell
with White Text
cell.Interior.ColorIndex = 15
cell.Font.ColorIndex = 2
Else
Set rng = Nothing
On Error Resume Next
Set rng =
Worksheets("Summary").Range("F2:F100").Find(cell.V alue)
On Error GoTo 0
If Not rng Is Nothing Then
cell.Interior.ColorIndex = rng.Interior.ColorIndex
End If
End If
End If
Next cell
End Sub

Sub clearcells()
Dim cell As Range
For Each cell In Worksheets("Details").Range("J3:V74")
cell.Interior.ColorIndex = xlAutomatic
Next cell
End Sub

Run via 2 command buttons - one to set the colours, the other to clear
them.

Chris

"Bob Phillips" wrote:

Sub colourcells()
Dim cell As Range
Dim rng As Range
For Each cell In Worksheets("Sheet1").UsedRange
Set rng = Nothing
On Error Resume Next
Set rng = Worksheets("Sheet2").Cells.Find(cell.Value)
On Error GoTo 0
If Not rng Is Nothing Then
cell.Interior.ColorIndex = rng.Interior.ColorIndex
End If
Next cell
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ChrisO" wrote in message
...
Hello,
I want to do the following:

- in sheet 2 a column of cells (could be up to 100) contains unique 4
character text codes, e.g. ABXY, each cell is coloured manually by the
user
to any background colour they like.
- in sheet 1 a range of cells each contain one of the 4 character
codes.
- I would like to colour the cells in sheet 1 to the same colour as
the
corresponding cell for the code in sheet 2.

I know this is going to require VBA which I'm not familiar with, so
I'd be
very grateful for your help and suggestions.

Thanks, Chris