C&P format & formula if certain data entered

[countryfan_n]

Hello Friends, I need a code please.

A. If anything is typed in column B (starting from B5). the code will copy the format and formulas of range (C3:J3).

And will paste it in the row that was typed in the column B row.


B. Now if the cell in column B was cleared (deleted / emptied). then the range (C?:J?) needs to be "deleted shift up".

Thanks a lot in advance!
Nawaf

[SamT]

A)
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)

Dim TargetPoint As Range
Set TargetPoint = Range("B5:B65536")
If Not Intersect(Target , TargetPoint) Then Exit Sub

Dim CopyRange As Range
Set CopyRange = Range("C3:J3")
Dim PasteRow As Long
PasteRow = Target.Row
Dim PasteColumn As Long
PasteColumn = 3

CopyRange.Copy destination:=Cells(PasteRow, PasteColumn)
[/VBA]

B)Say that you have Values and Formulas in Range (B5:J20).
If "ClearContents" in Range( B10) Then
Shift Formulas in Range (C11:J11) up to Row 10

For sure, all the Cells below (C11:J11) follow shift up.

Is that correct question?

[mdmackillop]

[VBA]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Source As Range
If Not (Target.Column = 2 And Target.Row > 4) Then Exit Sub
Application.EnableEvents = False
Set Source = Range("C3:J3")
If Not Target = "" Then
Source.Copy
Target.Offset(, 1).PasteSpecial xlPasteFormulas
Target.Offset(, 1).PasteSpecial xlPasteFormats
Target.Select
Else
Target.Resize(, 9).Delete xlUp
End If
Application.EnableEvents = True

End Sub
[/VBA]

[SamT]

Malcom,

I've seen what looks like several uses for "Resize." This one

Target.Resize(, 9)

looks like what i would expect it to be used for, ie. make the Range(Br) = Range(Br:Jr)

What are the other uses or "Resize?"

[mdmackillop]

Basically resize just changes the size of a range. You can combine it with Offset using variables or constant values to more easily refer to a range to be copied/processed.

[SamT]

Thanks

Sam