Try Worksheet_Change instead of Worksheet_SelectionChange.

"Packman" wrote:

Hi,

I'm trying to use VBA to change the date format of a cell when a
preceeding cell is changed. I am using data validation so it is a list
box, and the contents of the list box change depending on if the
variable "annual" or "monthly" is selected:

=IF($N$3="Annual",DATE_RANGE_ANN,DATE_RANGE_MON)

Here is the code behind the sheet I am trying to run when cell(N3) is
selected:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$N$3" Then

If Target.Value = "Annual" Then

Target.Offset(0, -1).NumberFormat = "yyyy"

Else

Target.Offset(0, -1).NumberFormat = "mmmm-yyyy"

End If

End If

End Sub


I should point out that both cells N3 and N4 are merged cells. I was
not sure if that would create a problem. Any help is greatly
appreciated...thanks.