Get range from highlighted cells in excel 2

[nalmond]

Hi guys,
I'm hoping this is going to be straightforward:
In excel, via vba, how do I interact with the highlighted cells on a worksheet. ie how do I highlight some cells on a worksheet and then use a macro, or vba code, to perform an action on each of the selected cells.

 

[VulcanJedi]

nalmond,

dependant on how the sht is laid out...

for each cl in range("A1:Z26&quot
if cl.interior.colorindex = 37 (your color here) then
EXCECUTE SOMETHING ELSE HERE
end if
next cl Tranpkp
************************************
- Let me know if this helped/worked!

 

[nalmond]

Cheers Tranpkp for the post. I probably didn't explain myself clearly enough. The problem I'm having is getting the cell range, eg (A1:F56) from the highlighted/selected portion of a sheet. I'm getting confused with properties and methods such as selection, selected, activecell etc.

 

[VulcanJedi]

I don't think the solution is out of reach, but specifically what are you asking for?
How to determine which cells are highlighted (or is this arelady known)?
How to reference cells?
?s are a little ambiguous and hard to help. Tranpkp
************************************
- Let me know if this helped/worked!

 

[kylua]

Sub anyoldname()
Dim mycell As Variant
For Each mycell In Selection
mycell.Interior.ColorIndex = 5'or whatever you want to do here
Next
End Sub

 

[DaleWatson123321]

I suspect this may be what you're seeking...

With Selection
'insert here whatever you want to
'perform on the selection
End With

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rudo]

Hi Nalmond,
Do you know the special macro in Worksheets, called SelectionChange? If you want to know which cell the user has selected, you can put following code in this macro:
r = TargetRow
c = TargetColumn
From there you write code in the macro to execute immediately the actions to be taken on the selected cell.
If you need exemple code pls let me know.

Greetings from Holland,
Rudo

 

[nalmond]

Thanks to all that have responded. I think the solution to my problem lies in a combination of Kylua and Rudo's posts. Kylua, thanks very much that code works a treat for interacting with the selected cells. The final step is to grab the range from the selected cells. Rudo is there a 'used range' / 'selected range' object that can be used with selection to grab the range of the highlighted cells on the worksheet? A rather pointless example of what I'm after would be: to highlight some cells on a worksheet and run a macro to turn them blue, this I can do thanks to Kylua, but also produce a msgbox showing the range for the highlighted cells. All help is gratefully received, and thanks again to those who have already helped.

 

[rudo]

Hi Nalmond,

I suppose this is what you want:

Click on a cell, which has to turn in blue and perform eventually other actions on the values of those cells.
Probably you will need a command, to turn this function on and off.
You could proceed like this:

For the On/Off command:
In Excel choose Menu View, Toolbars, Formulas (I am translating from dutch, the real names may be slightly different).
On the toolbar choose the groupform (xyz), and drag a small sqare on your Excelsheet.
Then, from the same toolbar, click the small "choiceround", and drag one in the square.
Take another one and do the same. Position it below the first one.
Now if you click on the buttons, one will turn black and the other white and vice versa.
Click the upper button to make it black. Then rightclick on this button. In the appearing menu, choose the last option (in english something with format...?)
On the last tab, you find Value: Turned off, turned on. Choose Turned on. Click in the textbox "related to cell" and click on the worksheet in cell A1 (or any cell you don't use).
Click OK.
Now if you use your buttons, in cell A1 the values 1 or 2 will appear, depending on the button that is "On".

To use this in SelectionChange:
Choose Tools, Macro, VisualBasic Editor
On the left you find ProjectExplorer (If it isn't there, find the button of that name to make it appear)
Doubleclick on Sheet1 (or whatever sheet you are working on).Above the window on the right, you find "General". In this box, select "Worksheet".
The "frame" for the Private Sub SelectionChange will appear. Add following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set ws1 = Workbooks("TheNameOfYourWorkbook.xls&quot.Sheets("Blad1&quot

r = Target.Row
c = Target.Column

'Note:
'the values of Target.Row and Target.Column are
'automatically known by this sub as soon as you click
'in a cell. This macro starts each time you click in a
'(different) cell.

If ws1.Cells(1, 1).Value = 1 Then
'this is the value of the on'off buttons in A1
ws1.Cells(r, c).Interior.ColorIndex = 5
'colors cell in blue

'if you want to remove color when function blue is
'turned off, you can add
'Else 'if function is “Off”
'ws1.Cells(r, c).Interior.ColorIndex = xlNone
End If

End Sub

Tell me if it works like you wanted.

Greetings,

Rudo (Holland)

 

[xlbo]

Hee Hee - methinks you boys are thinking too hard
Nalmond, all you need to get the range currently selected on a sheet is:
msgbox selection.address

to view it on a message box or

dim SelRange as range
SelRange = selection.address
to assign to a variable Rgds
~Geoff~

 

[nalmond]

Doh! xlbo methinks you are right! Thanks very much that is exactly what I was looking for. Thanks also to all the others who have helped.