Excel 2007 Auto format column conditional on another column 2

[BxWill]

Have a 3 column 2500 row Excel 2007 worksheet;

Name---Type------Value
Jane---Cost------1234
Jill---Service---1234

Is it possible to auto-format the figures in the column titled "Value" conditional on what is in the second column.

For example, if "Cost' is in the second column, the figure in the column titled "Value" should be formatted as Currency with two decimal places. If "Service" is in the second column, the figure in the column titled "Value" should be formatted as a general number without any decimal places.


did create the following, but decided to post to gather additional insight to determine if I am on the right path.

Is this how you would tackle this?

Any additional insight is appreciated.

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Count > 1 Then Exit Sub 
If Not Intersect(Target, [A1]) Is Nothing Then 
Set RNG = Range("B2:B2500") 
	Select Case Target 
		Case "Cost" 
			RNG.NumberFormat = "#,##0.00;[Red]-[$809]#,##0.00" 
		Case "Service" 
			RNG.NumberFormat = "#,##000 [$€-1];[Red]-#,##0.00 [$€-1]" End Select 
End If 
End Sub

 

[SkipVought]

hi,

Here's how I would do it...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.Count > 1 Then Exit Sub
    
    Set rng = Cells.Find("Type").Offset(1)
    
    If Not Intersect(Target, Range(rng, rng.End(xlDown))) Is Nothing Then
        Select Case Target
            Case "Cost"
                Target.Offset(0, 1).NumberFormat = "#,##0.00;[Red]-[$809]#,##0.00"
            Case "Service"
                Target.Offset(0, 1).NumberFormat = "#,##000 [$€-1];[Red]-#,##0.00 [$€-1]"
        End Select
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Here's how I would do it:

Conditional Formatting.

You can format number display on conditional formatting!

 

[SkipVought]

Gruuuu, U R absolutely correct!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Did slightly change the structure of the data.

In column B, from B5 to B246, I have one of the following text;
Cost
Service

In the range from E5 to J246, I have various figures that are not formatted.

I would like to "auto format" all of the figures in the range E5 to J246 based on the text that is in column B.

For example, if column B contain "Cost", then I would like the entire row from column E to column J to format as $10,000.
If column B contain "Service", then I would like the entire row from column E to colum J to format as

For example, if "Cost' is in the second column, the figure in the column E to column J should be formatted as Currency with two decimal places. If "Service" is in the second column, the figures in column E to column J should be formatted as a general number without any decimal places.

How should the following code be modified?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.Count > 1 Then Exit Sub

    Set rng = Cells.Find("Type").Offset(3)

    If Not Intersect(Target, Range(rng, rng.End(xlDown))) Is Nothing Then
        Select Case Target
            Case "Cost/Service"
                Target.Offset(0, 1).NumberFormat = "#,##0.00;[Red]-[$809]#,##0.00"
            Case "Services"
                Target.Offset(0, 1).NumberFormat = "#,##000 [$€-1];[Red]-#,##0.00 [$€-1]"
        End Select
    End If
End Sub