Hi Tom,

Thank you for your time and assistance - code works well.

Cheers
Tinä

Tom Ogilvy Wrote:
Right click on the sheet tab of the sheet containing the named range
and
select view code. Put in code like this

Private Sub Worksheet_Calculate()
MsgBox "In calculate"
Dim cell As Range
For Each cell In Me.Range("WIP")
cell.Offset(0, 5).Interior.ColorIndex = _
cell.Interior.ColorIndex
Next
End Sub

It will only fire when there is a recalculation, so just changing
color in
a cell in column C won't get updated (until a calculation occurs).

--
Regards,
Tom Ogilvy


"Tinä" wrote in message
...

Hi,

I've looked at various examples of using the VBA Interior.ColorIndex
but cannot get the background colour from one Cell to be put i
another
Cell. I hope this will explain what I'm trying to do:

I have two Columns using Dynamic Named Ranges - Column "C" is "WIP
and
Column "H" is "Cost" .
Column "C" with text and various background colours and "H" with
numbers and no background colours. The data in both Columns start
from
Row 8. I need the Cells in Column "H" to take on the sam
background
colour as the corresponding Cells in Column "C" on the same Row. I
cannot use either of the Columns data as criteria because the dat
is
so varied but I would like to make a link with the cells using
background colour just based on the Cells being on the same Row; i.e
background colour of Cell "C8" to match colour of Cell "H8".

The Columns are dynamic and the Rows will constantly increase in
length.
I would like the background colour matching to be done without m
first
having to manually select the cells on the worksheet.

Can VBA make the selections and colour change without m
intervention,
and as new Cells are added and coloured in Column "C" automatically
update the corresponding Cell/Row in Column "H" with the background
colour from Column "C" Cell/Row. Also can VBA do the above by using
the Dynamic Named Ranges "WIP" and "Cost" rather than the individual
Column/Cell references?

The information for the Defined Names Refers to Box
=OFFSET('Divisions'!$C$6,2,0,COUNTA('Divisions'!$C :$C),1) This is
name "WIP"
=OFFSET('Divisions'!$H$6,2,0,COUNT('Divisions'!$H: $H),1) Thi
is
name "Cost"

Cheers
Tinä

--
Tinä

------------------------------------------------------------------------

[/color

--
Tin
-----------------------------------------------------------------------
Tinä's Profile: http://www.excelforum.com/member.php...fo&userid=1541
View this thread: http://www.excelforum.com/showthread.php?threadid=27740