Macro formatting

Posted by RoB on August 14, 2001 3:09 PM

Yet another question! I have the following code which fills the cell to the right if data is entered. I would like the row that is being filled to be formatted as "mmmm" (month only). (the cell being filled from is formatted as mm/dd/yy). I thought this would work, then just format the filled row as "mmmm", but it seems the macro overrides the column format. when it fills, it copies exactly as the first column.

how do i specify formatting in a macro? here is my macro:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then
If Target.Columns.Count &LT; 2 Then
If Target &LT;&GT; "" Then

With Target

.Offset(0, 1).FillRight

End With

End If
End If
End If

End Sub

Thanks!

Posted by Robb on August 14, 2001 5:28 PM

Rob

Try this instead of FillRight:

.Offset(0, 1) = Format(Target, "mmmm")

Doeas that do the trick?

Posted by RoB on August 14, 2001 5:52 PM

Another questions

Thanks Robb!! that works perfectly. But I do have another question. Is there anyway to apply this formula to a list which already has data? ie. the month appears when I input a new date, but what about cells BEFORE i had the macro in place? I know if I edit the cell and press enter, it changes, but what if I have 500 cells like this?

Thanks again!

Posted by Robb on August 14, 2001 6:23 PM

Re: Another questions

Rob

I presume you only need to fill the 500 cells and that it will be a one off operation. If this is so, place this code in a module and run it - it should fill the cells for you (you'll need to use the correct sheet name)

Sub MMM()
With ActiveWorkbook.Worksheets("Sheet1").UsedRange.Cells
For Each c In Columns(4).Cells
If IsEmpty(c) = False Then c.Offset(0, 1) = Format(c, "mmmm")
Next c
End With

End Sub

Does that help?

Regards

Posted by Rob on August 14, 2001 7:48 PM

that did it! Thanks so much