Selection Change event stopping copy/paste

[hungledink]

I have a sheet with data in range a1 to a6 of usernames. Then in range b1 to z1 I have data relating the users. This is currently a test workbook that I'm working out the code for a much larger workbook.

I've created a selection change event macro which changes the font to bold and colour to red for any cell in range a1 to a6 depending on if another cell in that row is selected. The code then changes the font and colour back to normal when a cell in another row is selected.

So for example, select cell b1 causes cell a1 text to be bold and colour red. Then select cell c2, a1 would no longer be bold or red, but cell c1 would be.

This works as I want but it means I can no longer copy and paste any of the data from b1 to z1.

Is there a way to get round this?

 

[Yongle]

Please post your Selection_Change event macro

When replying please click on # icon above reply window and paste your code between the code tags
[ CODE ] your code goes here [ /CODE ]

thanks

 

[hungledink]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet1.Range("a1:a6")
    .Font.Bold = False
    .Font.Color = vbBlack
End With
Cells(Target.Row, 1).Font.Bold = True
Cells(Target.Row, 1).Font.Color = vbRed
End Sub

 

[Yongle]

Try this which bypasses the code when user is about to paste

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = False Then
        With Range("A1:A6")
            .Font.Bold = False
            .Font.Color = vbBlack
        End With
        With Cells(Target.Row, 1).Font
            .Bold = True
            .Color = vbRed
        End With
    End If
End Sub

Note removal of sheet reference which is not required within the sheet module belonging to that sheet
- it is not incorrect, simply unnecessary

 

[hungledink]

Thanks for this but it doesnt quite work how I'd like. It does allow me to copy, and then paste, but I'd like the formatting to be applied to the destination row if possible.

So if I copy from row 1, cell A1 will be bold and red. I then decided to paste into row 3, I'd like cell A3 to become formatted (bold and red) and cell A1 to revert back to normal unformatted.

 

[Yongle]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1:A6"), Target) Is Nothing Then
        Application.CutCopyMode = False
        ActiveCell.Offset(, 1).Activate
        ActiveCell.Offset(, -1).Activate
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 6 Then Exit Sub
    If Application.CutCopyMode = False Then
        With Range("A1:A6").Font
            .Bold = False
            .Color = vbBlack
        End With
        With Cells(Target.Row, 1).Font
            .Bold = True
            .Color = vbRed
        End With
   End If
End Sub

 

[hungledink]

Thanks for that but it didn't seem to do anything different from the previous version ?

 

[Yongle]

It works perfectly for me - but I may be testing something different to what you are actually doing

Which range are you copying?
Where are you pasting it to?
How are you doing this?
After pasting in which row is the cursor?
Which version of Excel are you using?