I have roughly 100 values I need to format cells with using the if
function. Is this possible? I need the cell to display each 'if' value
when the relevant figure is inserted.

Thanks


Phil


--
phil2006
------------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=558775

Hi Phil,

I have roughly 100 values I need to format cells with using the if
function. Is this possible? I need the cell to display each 'if' value
when the relevant figure is inserted.

Try something like:

'=============
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rcell As Range
Const sAdd As String = "A1:A10"

Set rng = Intersect(Range(sAdd), Target)

For Each rcell In rng.Cells
With rcell
Select Case .Value
Case 1: .Value = "Anne"
Case 2: .Value = "Ben"
Case 3: .Value = "Carol"

'................
'................
Case 100: .Value = "Xavier"
End Select
End With
Next rcell
End Sub

'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman

Thanks. How do you set the values though? For example, if I want it to
show Anne every time the value in the cell is 1.2 where do I insert
this?

Thanks again,

Phil


--
phil2006
------------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=558775

Phil,
If you that many items, why not use VLOOKUP. So, if A1="Anne", in A2 you
have:
=VLOOKUP(A1,rngData,2,False)
where rngData is a named range of:
Anne 1.2
Bill 1.5
John 0.8

NickHK

"phil2006" wrote in
message ...

Thanks. How do you set the values though? For example, if I want it to
show Anne every time the value in the cell is 1.2 where do I insert
this?

Thanks again,

Phil


--
phil2006
------------------------------------------------------------------------
phil2006's Profile:
http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=558775

Thanks. I have been trying the vlookup with no success! What I need i
an if function that says if the value is 1.49 and <1.51 it gives 1/2
if the value = 5 it returns 5/1 etc...

Thank

--
phil200
-----------------------------------------------------------------------
phil2006's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=55877

Phil,
I don't see how this fits in with the previous requirement of Anne=1.2.
Confused.

NickHK

"phil2006" wrote in
message ...

Thanks. I have been trying the vlookup with no success! What I need is
an if function that says if the value is 1.49 and <1.51 it gives 1/2,
if the value = 5 it returns 5/1 etc...

Thanks


--
phil2006
------------------------------------------------------------------------
phil2006's Profile:
http://www.excelforum.com/member.php...o&userid=35092
View this thread: http://www.excelforum.com/showthread...hreadid=558775