I have solved the problem, for the post:
get colour from one cell and change another cell to that colour
http://www.mrexcel.com/board/viewtopic.php?topic=11410&forum=2&9
These VBA scripts may help others who struggle to succeed
Thanks to: technix, zzydhf, Nimrod
..::Tom Powley::..
<hr>
<hr>
Purpose:
Colour the active cell with the colour of another cell, which is 21 down, and 7 across.
Code:
Sub CellColourChange()
'
' CellColourChange Macro
' Macro recorded 14/06/2002 by Research Machines plc
'
ActiveCell.Interior.ColorIndex = ActiveCell.Offset(21, 7).Range("A1").Interior.ColorIndex
End Sub
<hr>
<hr>
Purpose:
This procedure will fire every time a value in Column E is changed. When activated
this procedure will change to corresponding cell in Column A's intertior.color
to match the color in E.
-select the sheet you want the procedure to work in
-right click on the tab of the sheet you want the procedure to work in
-left click on the "viewcode" that is displayed in drop down menu..
VBE will open
-paste procedure in the large right window of the VBE
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 5 Then Cells(.Row, 1).Interior.ColorIndex = .Interior.ColorIndex
End With
End Sub
..::Nimrod::..
<hr>
<hr>
I worked this one out for myself - my very first bit of VBA scripting!!
Purpose:
This macro will search through cells A1 to A20 and any cells that contain the same value as the active cell will be then coloured the same as the active cell.
Code:
Sub FindCellAndUseCol()
'
' FindCellAndUseCol Macro
' Macro recorded 15/06/2002 by -
'
With Range("a1:a20")
Set c = .Find(ActiveCell, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = ActiveCell.Interior.ColorIndex
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
<hr>
<hr>
get colour from one cell and change another cell to that colour
http://www.mrexcel.com/board/viewtopic.php?topic=11410&forum=2&9
These VBA scripts may help others who struggle to succeed
Thanks to: technix, zzydhf, Nimrod
..::Tom Powley::..
<hr>
<hr>
Purpose:
Colour the active cell with the colour of another cell, which is 21 down, and 7 across.
Code:
Sub CellColourChange()
'
' CellColourChange Macro
' Macro recorded 14/06/2002 by Research Machines plc
'
ActiveCell.Interior.ColorIndex = ActiveCell.Offset(21, 7).Range("A1").Interior.ColorIndex
End Sub
<hr>
<hr>
Purpose:
This procedure will fire every time a value in Column E is changed. When activated
this procedure will change to corresponding cell in Column A's intertior.color
to match the color in E.
-select the sheet you want the procedure to work in
-right click on the tab of the sheet you want the procedure to work in
-left click on the "viewcode" that is displayed in drop down menu..
VBE will open
-paste procedure in the large right window of the VBE
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = 5 Then Cells(.Row, 1).Interior.ColorIndex = .Interior.ColorIndex
End With
End Sub
..::Nimrod::..
<hr>
<hr>
I worked this one out for myself - my very first bit of VBA scripting!!
Purpose:
This macro will search through cells A1 to A20 and any cells that contain the same value as the active cell will be then coloured the same as the active cell.
Code:
Sub FindCellAndUseCol()
'
' FindCellAndUseCol Macro
' Macro recorded 15/06/2002 by -
'
With Range("a1:a20")
Set c = .Find(ActiveCell, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = ActiveCell.Interior.ColorIndex
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
<hr>
<hr>