I found this post by Dave Peterson in 2007. I want to set some conditional
number formatting similar to how this code works but want to execute the code
for columns d through o. I tried changing Me.Range("a:a") with
Me.Range("d:o") and got a compile error: "invalid use of me keyword".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

I copied the code and changed the range to "d:o" like this
Set myRng = Intersect(Target, Me.Range("d:o"))

and it worked fine...

"Steve" wrote:

I found this post by Dave Peterson in 2007. I want to set some conditional
number formatting similar to how this code works but want to execute the code
for columns d through o. I tried changing Me.Range("a:a") with
Me.Range("d:o") and got a compile error: "invalid use of me keyword".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

Good for you! Unfortunately, that doesn't help me much.

"Sheeloo" wrote:

I copied the code and changed the range to "d:o" like this
Set myRng = Intersect(Target, Me.Range("d:o"))

and it worked fine...

"Steve" wrote:

I found this post by Dave Peterson in 2007. I want to set some conditional
number formatting similar to how this code works but want to execute the code
for columns d through o. I tried changing Me.Range("a:a") with
Me.Range("d:o") and got a compile error: "invalid use of me keyword".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

This code is an event handler.

If you're changing the cells in columns D:0 manually, then this event will fire
for each of those changes.

But you have to put the code behind the worksheet that should have this
behavior.

Rightclick on the worksheet tab and select view code.

Paste the code in the code window (usually the righthand side) of the window
that just opened up.

And remove the code from where you had placed it before.

Steve wrote:

I found this post by Dave Peterson in 2007. I want to set some conditional
number formatting similar to how this code works but want to execute the code
for columns d through o. I tried changing Me.Range("a:a") with
Me.Range("d:o") and got a compile error: "invalid use of me keyword".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

--

Dave Peterson