Excel question
 

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

Excel question
 

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

"BillO" wrote:

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

Excel question
 

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


"BillO" wrote:

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

Excel question
 

=IF(A1=11,"Red",IF(A1=15,"Blue",IF(A1=18,"Green",I F(A1=23,"Violet",IF(A1=19,"Brown",IF(A1=21,"Black" ,""))))))
"BillO" wrote:

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

Excel question
 

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

"BillO" wrote:

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

Excel question
 

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


--
Regards,

Peo Sjoblom



"Toppers" wrote in message
...
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

"BillO" wrote:

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