View Full Version : C&P format & formula if certain data entered
countryfan_n
04-04-2010, 06:26 AM
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
A)
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)
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
04-04-2010, 08:05 AM
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
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
04-04-2010, 09:01 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.