automatically format cell based on content of another cell

[Guest]

I am trying to automatically format a cell to display its contents in either
a date or number format based on which letter is displayed in another cell in
the same row. I.E. cell 5a value is M, I need cell 5f to be in MMMYY
format.

 

[Bernie Deitrick]

You would need to use the Change event: copy the code below, right -click
the sheet tab, select "View Code" and paste the code into the window that
appears. I have included a second condition (N) to show how to do multiple
formats based on entries in column A.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Target, Range("A:A"))
If UCase(myCell.Value) = "M" Then
Cells(myCell.Row, 6).NumberFormat = "MMM YY"
End If
If UCase(myCell.Value) = "N" Then
Cells(myCell.Row, 6).NumberFormat = "0.00"
End If
Next myCell
End Sub

 

[Guest]

Bernie,

THanks for your help, Your a prince!!!!