VBA - private sub working in background

[cmefly]

Hi,

i'm trying to create a macro that works in the background of a worksheet.

I have 2 columns, A and B.

Column A has a pull down menu where the user can choose from 3 letters - A, B, or C.

Column B is an input cell where the user types in numeric value.

What i'm looking for is IF the user chooses A, then the value in column B should be formatted to 2 DECIMAL PLACES.

IF the user chooses B, then the value in column B should be formatted to 4 DECIMAL PLACES.

F the user chooses C, then the value in column B should be formatted to 5 DECIMAL PLACES.

this is what i have but i doesn't work properly b/c it keeps looping.....




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim z As Integer

For z = 1 To 907 ' number of rows i have

If Range("a" & z).Value = "A" Then
Range("b" & z).Select
Selection.NumberFormat = "0.00"
End If

If Range("a" & z).Value = "B" Then
Range("b" & z).Select
Selection.NumberFormat = "0.0000"
End If

If Range("a" & z).Value = "C" Then
Range("b" & z).Select
Selection.NumberFormat = "0.00000"
End If


Next z

End Sub

 

[p45cal]

perhaps use conditional forattibng instead?:

Excel Workbook
	A	B
23	A	2.35
24	B	2.3457
25	a	2.35
26	A	2.35
27	B	2.3457
28	C	2.34567
29	A	2.35
30	B	2.3457
31	C	2.34567
32	A	2.35
33	B	2.3457
34	C	2.34567
35	A	2.35
36	B	2.3457
37	C	2.34567
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B23	1. / Formula is =$A23="C"	Abc
B23	2. / Formula is =$A23="B"	Abc
B23	3. / Formula is =$A23="A"	Abc

You can't see the format in the conditional formatting red box above but you can see its results.
Select the whole range when adding the conditional formatting and enter the formulae as if it only pertained to the active cell (usually the topmost of the selection) and get the $s in the right place.