colors vba marco

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Colors on VBA / Marco

Colors on VBA / Marco

resolvedResolved · High Priority · Version 2010

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

RE: Colors on VBA / Marco

Gary i have done it.

thanks alot for your help!!!!!!!


 

Excel tip:

Change the Default Width of All Columns in Excel 2010

If you want to change the width of the columns in your Excel 2010 spreadsheet, making them either larger or smaller, here's how:

In the Cells group on the Home tab, click Format.

Hover over the section called Cell Size and a drop down list will appear, select Default Width from this list.

In the Standard Width dialog box, enter the size you want to set as the default width and click OK.

View all Excel hints and tips


Server loaded in 0.08 secs.