How to make identical cell datas active to allow changes

[caprey]

How to create a command formula that could allow me activate cells containing
identical data thus make changed data or color fill?

 

[Gary''s Student]

1. select the cell with the desired value
2. run this small macro:

Sub findum()
Dim v As Variant
v = ActiveCell.Value
Dim rfind As Range
Set rfind = Nothing
For Each r In ActiveSheet.UsedRange
If r.Value = v Then
If rfind Is Nothing Then
Set rfind = r
Else
Set rfind = Union(rfind, r)
End If
End If
Next
rfind.Select
End Sub

all cells with the desired value will be Selected.


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Without formulas or VBA...............

Select all then Edit>Find

What: your data

Find All

In the "found" dialog select top cell then SHIFT + End + Downarrow

To change format, hit Format>Cells and make the format change.

To change data hit F2, make changes then CTRL + ENTER


Gord Dibben MS Excel MVP

 

[Gary''s Student]

What you propose is very powerful.

Even implementing it in VBA completely eliminates the need for a loop.

 

[Rick Rothstein]

Without formulas or VBA...............

Select all then Edit>Find

What: your data

Find All

In the "found" dialog select top cell then SHIFT + End + Downarrow

You can also key-in Ctrl+A at this point to select all the items in the list
(you can do this immediately after hitting the Find All button as the focus
is already in the list it produced).

 

[Gord Dibben]

Thanks

The recorder makes short work of the operation.


Gord