rvillaneda
New Member
- Joined
- Aug 5, 2017
- Messages
- 6
Fairly new to VBA and need some help. I have the following data in excel sheet1 in the range C2:D10. I'd like to be able to double click on either the Category or the Volume cell within the range and have that data (for the respective row) copied to another location. I have found a way to do that via the VBA code below; however, I would also like that same selection to be shaded upon the double click. For example, if I double click on the cell containing value of "Carrots", I'd like that cell and the associated volume cell (i.e. 112) to be highlighted. Alternatively, if I end up double clicking a different row the highlight should turn off and only the newly selected should be highlighted.
The VBA code to transfer the selected data seems to be working well. How do I add code so I can get it to highlight column C and D for the selected row?
<tbody>
</tbody>
Current VBA Code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column >= 3 And Target.Column <= 4 And Target.Row >= 2 And Target.Row <= 10 Then
Cancel = True
If Target.Value <> "" Then
Sheet3.Range("B3") = Range("c" & Target.Row).Value
Sheet3.Range("C3") = Range("d" & Target.Row).Value
End If
End If
End Sub
The VBA code to transfer the selected data seems to be working well. How do I add code so I can get it to highlight column C and D for the selected row?
Category | Volume |
Bananas | 212 |
Carrots | 112 |
Zucchini | 153 |
Asparagus | 311 |
Potatoes | 112 |
Squash | 89 |
Beets | 45 |
Plums | 125 |
<tbody>
</tbody>
Current VBA Code
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column >= 3 And Target.Column <= 4 And Target.Row >= 2 And Target.Row <= 10 Then
Cancel = True
If Target.Value <> "" Then
Sheet3.Range("B3") = Range("c" & Target.Row).Value
Sheet3.Range("C3") = Range("d" & Target.Row).Value
End If
End If
End Sub