Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to match Cell Colour to another Cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can colour of cell shading be fixed to one colour | Excel Discussion (Misc queries) | |||
change a cell background colour to my own RGB colour requirements | Excel Discussion (Misc queries) | |||
How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
VBA to match Cell Colour to another Cell | Excel Programming | |||
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown | Excel Programming |