Change cell color according to other cells value/position

t.powley

Board Regular
Joined
Jun 13, 2002
Messages
76
:) 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>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Nice T!

Should we keep this post with the original thread?

If you respond to the original thread, it'll be bumped up for us to see and we can see the entire solution coming together.

Food for thought, makes it easier for people (like me) to follow...

Glad to hear all is well. Have a nice weekend.
 
Upvote 0
You are right,

I'll do it now

Hope this helps others and once again thank you everyone!!

..::Tom::..
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top