Copy and autopast rows from sheet1 to sheet2 IFClosed

Question

Hello, 

I woul like to 
Copy and autopast rows from sheet1 to sheet2 IF  they are f ex "highlighted" or "bold" or "underlined"

is something like this possible?

TrowaD · Answer

Hi Majk,

Have a look at the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub

If Target.Interior.ColorIndex > 0 Or Target.Font.Bold = True Or Target.Font.Underline = xlUnderlineStyleSingle _
Then Target.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub 

This code will look at column A and whenever a change is made to the cell contents, the code will check if the cell is highlighted, bold or underlined. If so, then the row is copied to the first available row of Sheet2.

The code will only work when a change is made to the cell contents, NOT the cell format. Change the letter A from code line 2 into the column letter you enter the last of your data. Apply the format before you enter the last of your data. When you apply the format after entering all your data for that row, you can still double click the cell from the chosen column and hit enter to confirm the cells content (which will count as a change and trigger the code).

Let us know if you have further questions.

Best regards,
Trowa

Majk · Answer

Hi.
Thnks

Dosnt seems to work correct.
Changed the sheet-name, got sub-error,
Walk me throu the setup here?    

Ragards

TrowaD · Answer

Hi Majk,

Have a look at the file below. I put 5 rows of data in Sheet1, with row 1 being the header. 2nd row contains no format, 3rd row is highlighted, 4th row is underlined and the 5th row is bold.

Double click cell A2 and hit enter. Do the same for A3, A4 and A5. Now goto Sheet2 and you will notice that the 3rd, 4th and the 5th row are copied here, but not the 2nd row.

Here is the file:
https://wetransfer.com/downloads/3398cfed2f53476c3bab93a89ecd2c9d20211109164317/ea4fe5

If you can't figure it out with my sample file, then consider uploading your own file (always be careful with sensitive data). 

Best regards,
Trowa

Copy and autopast rows from sheet1 to sheet2 IF

3 responses

Similar discussions

Subscribe To Our Newsletter!