Hi Bob,

I have a couple of supplementary questions this has thrown up:

1. My range in the Details sheet will always start at J3 but the table could
grow so can you tell me if there is a way to identify the max row and column,
and how I then use this in the range property?

2. I have noticed that after clearing the cell colouring the gridlines seem
to disappear for the affected cells. Can you tell me why, and how to stop
this happening?

Thanks, Chris

"Bob Phillips" wrote:

Excellent.

Regards

Bob

"ChrisO" wrote in message
...
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