I'm using Excel 2003.
To do some planning and I'm using 6 different codes, for instance MED, JAK,
KOF, NID, DOK, WIK.
For each code I have defined a different color, for instance the code JAK
gets the color red. The other codes have different colors.

So, what I want to happen is that, when for instance I type JAK in cel F8
(or any other cel in the excel-sheet), this cel F8 must get automatically
get the color red.

How can I do this?
I'm not a programmer, but I really need this.
Can somebody provide me with an example how to program this in VB?


Rickie

Do you need it in VBA?

Conditional Formatting will give you same function.

If in VBA, event code behind the sheet would do.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("D:D")) <---------change to suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Edit the A, B, C etc. to suit.


Gord Dibben MS Excel MVP
On Thu, 21 Feb 2008 21:44:08 +0100, "Rickie Gibson"
wrote:

I'm using Excel 2003.
To do some planning and I'm using 6 different codes, for instance MED, JAK,
KOF, NID, DOK, WIK.
For each code I have defined a different color, for instance the code JAK
gets the color red. The other codes have different colors.

So, what I want to happen is that, when for instance I type JAK in cel F8
(or any other cel in the excel-sheet), this cel F8 must get automatically
get the color red.

How can I do this?
I'm not a programmer, but I really need this.
Can somebody provide me with an example how to program this in VB?


Rickie

Here is some worksheet code you can experiment with:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
codes = Array("MED", "JAK", "KOF", "NID", "DOK", "WIK")
valuess = Array(6, 4, 41, 3, 38, 39)
v = t.Value
For i = 0 To 5
If v = codes(i) Then
Application.EnableEvents = False
t.Interior.ColorIndex = valuess(i)
Application.EnableEvents = True
Exit Sub
End If
Next
End Sub



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 - gsnu2007d


"Rickie Gibson" wrote:

I'm using Excel 2003.
To do some planning and I'm using 6 different codes, for instance MED, JAK,
KOF, NID, DOK, WIK.
For each code I have defined a different color, for instance the code JAK
gets the color red. The other codes have different colors.

So, what I want to happen is that, when for instance I type JAK in cel F8
(or any other cel in the excel-sheet), this cel F8 must get automatically
get the color red.

How can I do this?
I'm not a programmer, but I really need this.
Can somebody provide me with an example how to program this in VB?


Rickie

Gord and Gary,

Thank you both for the fast answers.
This weekend I'm going to experiment with the codes.

Rickie


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Do you need it in VBA?

Conditional Formatting will give you same function.

If in VBA, event code behind the sheet would do.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("D:D")) <---------change to
suit
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Edit the A, B, C etc. to suit.


Gord Dibben MS Excel MVP
On Thu, 21 Feb 2008 21:44:08 +0100, "Rickie Gibson"

wrote:

I'm using Excel 2003.
To do some planning and I'm using 6 different codes, for instance MED,
JAK,
KOF, NID, DOK, WIK.
For each code I have defined a different color, for instance the code JAK
gets the color red. The other codes have different colors.

So, what I want to happen is that, when for instance I type JAK in cel F8
(or any other cel in the excel-sheet), this cel F8 must get automatically
get the color red.

How can I do this?
I'm not a programmer, but I really need this.
Can somebody provide me with an example how to program this in VB?


Rickie