Excel question

B

BillO

Simple help needed, I want to make a cell return a value when another cell
has a particular value. for example

Part # color
11 red This cell = red, blue, … depending on what Part # is entered.
15 blue
23 violet
18 green
19 brown
21 black

Hope to hear back soon, thanks
 
T

Toppers

You will need VBA code (sample) below.

To enter, right click on worksheet tab, "View Code" and copy and paste code
below.

You will to change the WS_RANGE to that of your Part #s and then extend the
CASE statements..


-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

HTH
 
M

Mike

Assumes your part numbers are in A1 to A20, change to suit. paste this into
the worksheet code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Select Case Target
Case 11
icolor = 3
Case 15
icolor = 5
Case 18
icolor = 4
Case 19
icolor = 9
Case 21
icolor = 1
Case 23
icolor = 7
Case Else
'Anything
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

Mike
 
M

Mike

=IF(A1=11,"Red",IF(A1=15,"Blue",IF(A1=18,"Green",IF(A1=23,"Violet",IF(A1=19,"Brown",IF(A1=21,"Black",""))))))
 
M

Mike

=IF(A1=11,"Red",IF(A1=15,"Blue",IF(A1=18,"Green",IF(A1=23,"Violet",IF(A1=19,"Brown",IF(A1=21,"Black",""))))))
 
P

Peo Sjoblom

Or Excel 2007, there are no limits (except hardware) on the number of
conditions for conditional formatting in 2007
 
Top