Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to match Cell Colour to another Cell
Hi, I've looked at various examples of using the VBA Interior.ColorInde but cannot get the background colour from one Cell to be put in anothe Cell. I hope this will explain what I'm trying to do: I have two Columns using Dynamic Named Ranges - Column "C" is "WIP" an Column "H" is "Cost" . Column "C" with text and various background colours and "H" wit numbers and no background colours. The data in both Columns starts fro Row 8. I need the Cells in Column "H" to take on the same backgroun colour as the corresponding Cells in Column "C" on the same Row. cannot use either of the Columns data as criteria because the data i so varied but I would like to make a link with the cells usin background colour just based on the Cells being on the same Row; i. background colour of Cell "C8" to match colour of Cell "H8". The Columns are dynamic and the Rows will constantly increase i length. I would like the background colour matching to be done without me firs 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" automaticall update the corresponding Cell/Row in Column "H" with the backgroun colour from Column "C" Cell/Row. Also can VBA do the above by usin the Dynamic Named Ranges "WIP" and "Cost" rather than the individua Column/Cell references? The information for the Defined Names Refers to Box =OFFSET('Divisions'!$C$6,2,0,COUNTA('Divisions'!$C :$C),1) This i name "WIP" =OFFSET('Divisions'!$H$6,2,0,COUNT('Divisions'!$H: $H),1) This i name "Cost" Cheers Tin -- Tin ----------------------------------------------------------------------- Tinä's Profile: http://www.excelforum.com/member.php...fo&userid=1541 View this thread: http://www.excelforum.com/showthread.php?threadid=27740 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to match Cell Colour to another Cell
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 |
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) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
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) | |||
Conditional Format with VBA - Interior Colour of cell based on value from in-cell dropdown | Excel Programming |