Jay has attended:
Excel Intermediate course
Excel Advanced course
Colors on VBA / Marco
Hi
When i transfer one row to another using a Macro, if i have a row colored because of conditional formatting or a colored column. When i transfer it, it carries the color where i want to lose the color. in the row (just the fill color not the text).
This is the small code :
Sub MoveA()
Dim xrow As Long
xrow = 2
Sheets("Tickets N").Select
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Do Until xrow = lastrow + 1
ActiveSheet.Cells(xrow, 22).Select
If ActiveCell.Text = "Yes" Then
Selection.EntireRow.Cut
Sheets("Tickets Y").Select
ActiveSheet.Range("LastCell3").Select
ActiveSheet.Paste
Selection.Copy
Application.CutCopyMode = False
Sheets("Tickets N").Select
ActiveCell.Select
Selection.EntireRow.Delete
xrow = xrow - 1
End If
xrow = xrow + 1
Loop
End Sub
I think something needs to be change on : ActiveSheet.Paste
Regards
Jay
RE: Colors on VBA / Marco
Hi Jay
Thanks for getting in touch. Your instincts are right, the default Paste command takes the contents and the formatting.
You should change that line for
Selection.PasteSpecial Paste:=xlPasteValues
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Colors on VBA / Marco
Hi
Thanks for your response.
I thought i had tried this before i just tried it and it comes up with Error Debug on that new row.
what you thinking?
The rows has lots of formulas in it as well and different colors of text, because its just the Fill color i need removed when moving.
Regards
Jay
RE: Colors on VBA / Marco
I thought of these but don't know if it would make sense or work. As im pasteing ect...
Colorindex = 0
Selection.Interior.ColorIndex = xlNone
RE: Colors on VBA / Marco
Sorry to send another message through. but i think it may because i have a conditional formatting on that row, is there a way to ignore condition formatting when transferred?
i just tried :
Selection.Interior.Color = xlNone
ActiveSheet.Paste
didnt work either
RE: Colors on VBA / Marco
Hi Jay
To remove conditional formatting from a cell, use:
Selection.FormatConditions.Delete
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Colors on VBA / Marco
am i meant to be placing this in a certain place? because i put it before the ActiveSheet.Paste. Now when it transfers the row it just deletes the whole row?
regards
Jay
RE: Colors on VBA / Marco
Ok have way there,
Sub MoveA()
Dim xrow As Long
xrow = 2
Sheets("Tickets N").Select
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Selection.FormatConditions.Delete <------------------
Do Until xrow = lastrow + 1
ActiveSheet.Cells(xrow, 22).Select
If ActiveCell.Text = "Yes" Then
Selection.EntireRow.Cut
Sheets("Tickets Y").Select
ActiveSheet.Range("LastCell3").Select
ActiveSheet.Paste
Selection.Copy
Application.CutCopyMode = False
Sheets("Tickets N").Select
ActiveCell.Select
Selection.EntireRow.Delete
xrow = xrow - 1
End If
xrow = xrow + 1
Loop
End Sub
I have put in your code and it has just removed the Conditional formatting of that cell, i need it of the whole row
Regards
Jay
