Designating color in one cell based on data entered in another cel

[Guest]

I need a way (formula, format, etc) for the background color in one cell to
be determined by the data entered in another cell. For example: Cell C6
will have a number manually entered. Cell D6 will have a single letter
manually entered into it. Based on the letter entered in D6, I want the
background color in cell C6 to be a certain color. If the letter in D6 is P
then the background in C6 needs to be green. If the letter in D6 is I, then
the background needs to be red. I have 7 letters that will be entered into
cell D6 that will need to determine 7 different background colors for cell
C6. How can I make this happen using excel 2003?

 

[Don Guillett]

Suggest a SELECTCASE macro within a WORKSHEET_CHANGE event macro

 

[Guest]

You need VBA code or Excel 2007.

 

[Gord Dibben]

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Me.Range("D6")
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case 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
Case Is = "G": Num = 6 'yellow
End Select
'Apply the color
rng.Offset(0, -1).Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module. Edit letters and color numbers to
suit.


Gord Dibben MS Excel MVP