Updating cells based on selected range 1

[ChrizDK]

Hi,

I was wondering if the following was possible using VBA in Excel:

The user marks a range in a column - this could be a multiple selection using CTRL + LeftMouseButton.

I would then like to update the cell next to the selected range with e.g. a X - meaning that the cell was part of a selection.

Example:

The user marks the following cells:

A1, A2, A4, A8 and A10

I would then like to update:

B1, B2, B4, B8 and B10 with a X

I've tried several things but my selected range always gets "lost" when moving to the adjacent cell.

Hope someone can help me out.

Best wishes

Christian

 

[murad5]

Hi Chris,

This code should do the trick:

Sub marker()
Dim myRange As Range, cel As Range
Set myRange = Selection
For Each cel In myRange
cel.Offset(0, 1).Value = "x"
Next
End Sub

 

[ChrizDK]

Hi Murad5,

thanks for the code - it works fabulously.

Cheers and a star to you

Christian

 

[ChrizDK]

Murad5,

i've been using your code and has actually run into a limitation, that i'm quite sure that you can help me overcome.

If the sheet uses the autofilter function the cells included in the selection (when using 'drag' selection) is not only the cells visible but also any cells not in sight - thus updating more cells than one would expect. (When marking the cells one by one (CTRL) it works OK)

Been tryin the VB Guide but cannot find anything regarding the possibility to use a "SubSelection" or the like.

Hope that you can help me out (again)

Best wishes

Christian