View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
joemeshuggah joemeshuggah is offline
external usenet poster
 
Posts: 96
Default format on change event

revised the code as follows; but still cant seem to get the wrapping to work

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False



If Target.Column = 5 And Target.Row 2 And Target.Value < "" Then
With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp)
.Offset(1, 0).EntireRow = Target.EntireRow.Value
End With

Target.EntireRow.Delete

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(1, 5).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With


With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Offset(0, 4).WrapText = True
End With


End If
stoppit:
Application.EnableEvents = True
End Sub


"joemeshuggah" wrote:

i received some helpful code that upon change of a cell in one tab, the row
is moved to another tab. is it possible to preserve the formatting of the
row in the move to the next tab? i would think this would be relatively
simple, but cant seem to come close to getting it to happen (the row is 5
columns long, with a thin outline around each cell and wrapped text in the
last column).

if the above is not possible, i thought that this might work (formatting the
row in the new tab after the move has taken place), but im not sure where i
am going wrong.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False



If Target.Column = 5 And Target.Row 2 And Target.Value < "" Then
With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp)
.Offset(1, 0).EntireRow = Target.EntireRow.Value
End With

Target.EntireRow.Delete



With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(0, 5).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(0, 5).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(0, 5).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(0, 5).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Offset(0, 4).WrapText = True
End With

MsgBox Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Offset(0, 4).Address

End If
stoppit:
Application.EnableEvents = True
End Sub