Conditional Number Format

WELCOME TO THE BOARD!

You could try something like this:

If D6 is the test condition and E6 is the cell you want to format, you can add this formula to F6 to have the format that you want.

=IF(D6="",E6,TEXT(E6,"$#0.00"))

Otherwise, you can do it with a bit of VB code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim i As Integer
i = 1
Do Until Range("D" & i) = ""
If Range("D" & i).Value = 1 Then
Range("E" & i).Value = Format(Range("E" & i).Value, "$#0.00")
End If
i = i + 1
Loop
End Sub

Does this help?

I hate to step on someone's toes, but I think I need to here. If you use a VBA solution, you should put it in the Change event (as opposed to the SelectionChange event), something like this:<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Explicit</font><hr align=left width=500><font color='#000080'>Private</font><font color='#000080'>Sub</font> Worksheet_Change(ByVal Target<font color='#000080'>As</font> Range)<font color='#000080'>Dim</font> cl<font color='#000080'>As</font> Range<font color='#000080'>Dim</font> rngIntersect<font color='#000080'>As</font> Range<font color='#008000'>' Put the range of cells that you want to evaluate where</font><font color='#008000'>' I have Range("D:D"). So in this example, if a number in</font><font color='#008000'>' column D has a number greater than 1, then the cell in the</font><font color='#008000'>' same row in column E gets a Percentage format,</font><font color='#008000'>' otherwise it gets a General format.</font><font color='#008000'>' (Offset(0,1) gives the cell to the right of the current</font><font color='#008000'>' cl - for more info, look Offset up in VBA help).</font><font color='#008000'>' You can change this to make it change the cell(s) of your</font><font color='#008000'>' choice...</font><font color='#000080'>Set</font> rngIntersect = Intersect(Range("D:D"), Target)<font color='#000080'>If</font><font color='#000080'>Not</font> rngIntersect Is Nothing<font color='#000080'>Then</font><font color='#000080'>For</font><font color='#000080'>Each</font> cl In rngIntersect<font color='#000080'>If</font> cl.Value > 1<font color='#000080'>Then</font>
cl.Offset(0, 1).NumberFormat = "0.00%"<font color='#000080'>Else</font>
cl.Offset(0, 1).NumberFormat = "General"<font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>Next</font> cl<font color='#000080'>End</font><font color='#000080'>If</font><font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>

-

If you use the selection change event, then your event will fire (and check all cells with text in column D) every time you move from cell to cell. You would most likely only want the formats to change if one of the dependant cells changes.

Also, the code above needs to go into the Worksheet module. To learn a little bit about workbook/worksheet modules, click HERE

HTH,

Russell
This message was edited by Russell Hauf on 2002-11-14 18:43

thanks, but the formula isn't exactely what I want. Ideally I want the format change to occur in cell E6, where the value was originally inputed.

the value keeps coming back in the cell where i enter the formula i.e. F6, i don't know if this is possible with a formula?

Ok, easy enough. It sounded in your original message that you wanted to change one cell based on another.

Just take out the Offset part:


HTH,

Russell

Maybe just try using conditional formatting. In the "use a formula to determine which cells to format" type =$C$45=>1 (assuming c45 is the cell that we are looking to see if it is greater than 1). Then go to "Format" button. Choose "Number" and then choose the cell number format of your choice from the list available.

BC