automatically format cell based on content of another cell
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. |
automatically format cell based on content of another cell
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 "Blustreaker" wrote in message ... 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. |
automatically format cell based on content of another cell
Bernie,
THanks for your help, Your a prince!!!! "Bernie Deitrick" wrote: 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 "Blustreaker" wrote in message ... 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. |
All times are GMT +1. The time now is 03:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com