Colour Index
 

Hi,

Sorry yet another simple question.

I have numbers, (1,2,3 etc) in cells I5 to I9 from which I would like to
generate a colour in cells B5-9, (black, white, red etc). If I change I6
from 4 to 9 the colour in B6 updates as soon as I hit return or the down key
after entering the number.

What is the easiest way to do this?
Thanks

Colour Index
 

You need an event sub for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Row <= 9 And Target.Column = 9 Then
Range("B" & Target.Row).Font.ColorIndex = Target
End If
End Sub


Regards,
Stefi

€žLiAD€� ezt Ã*rta:

Hi,

Sorry yet another simple question.

I have numbers, (1,2,3 etc) in cells I5 to I9 from which I would like to
generate a colour in cells B5-9, (black, white, red etc). If I change I6
from 4 to 9 the colour in B6 updates as soon as I hit return or the down key
after entering the number.

What is the easiest way to do this?
Thanks

Colour Index
 

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set i = Range("I5:I9")
Set t = Target
If Intersect(t, i) Is Nothing Then Exit Sub
v = t.Value
If v 56 Then Exit Sub
Application.EnableEvents = False
Cells(t.Row, "B").Interior.ColorIndex = v
Application.EnableEvents = True
End Sub

so if you enter a number between 1 and 56 in column I, the cell in column B
will change to that number.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200830


"LiAD" wrote:

Hi,

Sorry yet another simple question.

I have numbers, (1,2,3 etc) in cells I5 to I9 from which I would like to
generate a colour in cells B5-9, (black, white, red etc). If I change I6
from 4 to 9 the colour in B6 updates as soon as I hit return or the down key
after entering the number.

What is the easiest way to do this?
Thanks

Colour Index
 

Hi,

Sorry I should have been more specific.

I was hoping to colour the cell and the text. How do I adapt the code to do
both?

Thanks

"Stefi" wrote:

You need an event sub for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Row <= 9 And Target.Column = 9 Then
Range("B" & Target.Row).Font.ColorIndex = Target
End If
End Sub


Regards,
Stefi

€žLiAD€� ezt Ã*rta:

Hi,

Sorry yet another simple question.

I have numbers, (1,2,3 etc) in cells I5 to I9 from which I would like to
generate a colour in cells B5-9, (black, white, red etc). If I change I6
from 4 to 9 the colour in B6 updates as soon as I hit return or the down key
after entering the number.

What is the easiest way to do this?
Thanks

Colour Index
 

You can set the background color this way
Range("B" & Target.Row).Interior.ColorIndex = Target

But in one cell (I5:I9) you can have only one ColorIndex. Setting both
foreground and background color to the same value makes the cell content
invisible.

Regards,
Stefi

€žLiAD€� ezt Ã*rta:

Hi,

Sorry I should have been more specific.

I was hoping to colour the cell and the text. How do I adapt the code to do
both?

Thanks

"Stefi" wrote:

You need an event sub for that:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Row <= 9 And Target.Column = 9 Then
Range("B" & Target.Row).Font.ColorIndex = Target
End If
End Sub


Regards,
Stefi

€žLiAD€� ezt Ã*rta:

Hi,

Sorry yet another simple question.

I have numbers, (1,2,3 etc) in cells I5 to I9 from which I would like to
generate a colour in cells B5-9, (black, white, red etc). If I change I6
from 4 to 9 the colour in B6 updates as soon as I hit return or the down key
after entering the number.

What is the easiest way to do this?
Thanks