VBA to match Cell Colour to another Cell
 

Hi Tom,

Much appreciated - i'll give it a try.

Thanks
Tinä

Tom Ogilvy Wrote:
Calculate doesn't return any reference to the cell that caused the
event, so
it isn't possible with calculate. Depending on when you need to update
and
how cells change, you can perhaps use the change event with intersect

if not intersect(target,Range("A1:Z26")) is nothing then
' the trigger cell (target) is within the range of interest

end if
--
Regards,
Tom Ogilvy

"Tinä" wrote in message
...

Hi Tom,

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

Is it possible to use If Not Intersect with the Private Sub
Worksheet_Calculate(), so that the actual colour change would only
take
place when there is a recalculation specific to the Named Ranges: WIP
or
Cost.

If the above is possible, please show how?

Thanks
Tinä


Tom Ogilvy Wrote:
Right click on the sheet tab of the sheet containing the named
ranges
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
a
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 in
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
starts
from
Row 8. I need the Cells in Column "H" to take on the same
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
data
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
me
first
having to manually select the cells on the worksheet.

Can VBA make the selections and colour change without my
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)
This
is
name "Cost"

Cheers
Tinä

--
Tinä


------------------------------------------------------------------------
Tinä's Profile:
http://www.excelforum.com/member.php...o&userid=15410
View this thread:
http://www.excelforum.com/showthread...hreadid=277406


--
Tinä

------------------------------------------------------------------------
Tinä's Profile:
http://www.excelforum.com/member.php...o&userid=15410
View this thread:
http://www.excelforum.com/showthread...hreadid=277406



--
Tinä
------------------------------------------------------------------------
Tinä's Profile: http://www.excelforum.com/member.php...o&userid=15410
View this thread: http://www.excelforum.com/showthread...hreadid=277406