Change Format (Excel XL)

Topic

New Reply

Hi All,

I have a similar question to that answered by Steve in this post but my formatting condition is based on another cell. For example, if Cell B2 = 1, then I want column D to be formatted as a percentage (with 1 decimal place). If, however, Cell B2 = 2, then I want column D to be formatted as a number (with 3 decimal places).

Reply | Quote

Replies

How do you want column D formatted if B2 is anything other than 1 or 2?

Reply | Quote

You could put the following code in the worksheet change event routing for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(“B2”)) Is Nothing Then Exit Sub
If Range(“B2”).Value = 1 Then
Range(“D:D”).NumberFormat = “0.0%”
Else
If Range(“B2”).Value = 2 Then
Range(“D:D”).NumberFormat = “0.000”
Else
Range(“D:D”).NumberFormat = “General”
End If
End If
End Sub

Not having gotten an answer to my last post yet, this code will format column D in General format if B2 is anything other than 1 or 2.

Reply | Quote

(Edited by sdckapr on 08-Jul-05 14:40. oops fixed my formatting reversed the 2 possibilites…)

You could add something like this in the worksheet object:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim rCell As Range
    Set rCell = Range("B2")
    If Not Intersect(Target, rCell) Is Nothing Then
        Set rng = Range(Range("D1"), Range("D65536").End(xlUp))
        With Application
            .EnableEvents = False
            With rng
                Select Case rCell.Value
                    Case Is = 1
                        .NumberFormat = "0.0%"
                    Case Is = 2
                        .NumberFormat = "0.000"
                    Case Else
                        .NumberFormat = "General"
                    End Select
            End With
            .EnableEvents = True
        End With
    End If
    Set rCell = Nothing
    Set rng = Nothing
End Sub

It will convert the cells to general if it is neither 1 nor 2 (change as desired)…

Steve

Reply | Quote

I’m not completely sure what was wanted. Your code only formats cells down to the last one that currently has data. If additional data is added to the end of column D, it will not be formatted until cell B2 is changed again. If that is what is wanted, then that is fine.

Reply | Quote

Yes that was mine intent. (I do not like formatting more than necessary since it sometimes affects the UsedRange…)

I was not sure either what is desired. It can be modified with either code easily enough.including other values for B2.

Steve

Reply | Quote

Legare / Steve – thx for the replies and sorry for not seeing your earlier ones Legare. The cell / column was only to illustrate the concept.

I was thinking of an excel in-cell formula something like conditional formating (or similar) rather than via VBA but it looks like the solution will have to be via VBA.

Reply | Quote

Cell number formatting is based on cell contents not the contents of another cell.

Conditional formatting does not affect the cell number format in any way.

Steve

Reply | Quote

Ok, thx Steve

Reply | Quote

A formula option is to leave D alone and display as desired in Col E (though COl E would be text). You could all the calcs with D, but use E in printouts/display:
E1:
=IF(ISBLANK(D1),””,IF($B$2=1,TEXT(D1,”0.0%”),IF($B$2=2,TEXT(D1,”0.000″),TEXT(D1,”General”))))

Copy down the column as desired…

Steve

Reply | Quote