slamet Harto
11-19-2008, 08:29 PM
Hi guys,
Kindly advise what I'm doing wrong with the following code:
For instance: Range I4 has a formula to be copied to range I5:I10.
However, some of cells in range I5:I10 has colour / background that no need to paste.
Thank you for your assistance.
Rgds, Harto
Here is what I've so far:
Sub PasteNoColour()
Dim RangeBiruasin As Range
Dim xRowsCount As Long
Dim r As Long
On Error Resume Next
ActiveCell.Copy 'copy cell I4
Set RangeBiruasin = Range("I5:I10") ' Application.InputBox( _
'Prompt:="Select Range to be processed.!", _
'Title:="Copy Paste formula except colour", _
'Default:=Selection.Address, Type:=8)
xRowsCount = RangeBiruasin.Rows.Count
Application.Calculation = xlCalculationManual
For r = xRowsCount To 1 Step -1 'pls advice me whether we need to use "STEP-1" ?
If RangeBiruasin(r, 1).Interior.ColorIndex <> 34 Then
RangeBiruasin(r, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Application.StatusBar = " . . . . . . . . . . . . . . . . . . Scanning Row:" & Str(r)
Next r
Application.Calculation = xlCalculationAutomatic
pplication.StatusBar = ""
End Sub
Kindly advise what I'm doing wrong with the following code:
For instance: Range I4 has a formula to be copied to range I5:I10.
However, some of cells in range I5:I10 has colour / background that no need to paste.
Thank you for your assistance.
Rgds, Harto
Here is what I've so far:
Sub PasteNoColour()
Dim RangeBiruasin As Range
Dim xRowsCount As Long
Dim r As Long
On Error Resume Next
ActiveCell.Copy 'copy cell I4
Set RangeBiruasin = Range("I5:I10") ' Application.InputBox( _
'Prompt:="Select Range to be processed.!", _
'Title:="Copy Paste formula except colour", _
'Default:=Selection.Address, Type:=8)
xRowsCount = RangeBiruasin.Rows.Count
Application.Calculation = xlCalculationManual
For r = xRowsCount To 1 Step -1 'pls advice me whether we need to use "STEP-1" ?
If RangeBiruasin(r, 1).Interior.ColorIndex <> 34 Then
RangeBiruasin(r, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If
Application.StatusBar = " . . . . . . . . . . . . . . . . . . Scanning Row:" & Str(r)
Next r
Application.Calculation = xlCalculationAutomatic
pplication.StatusBar = ""
End Sub