How to make identical cell datas active to allow changes
 

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

How to make identical cell datas active to allow changes
 

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

--
Gary''s Student - gsnu200821


"caprey" wrote:

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

How to make identical cell datas active to allow changes
 

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

Select all then EditFind

What: your data

Find All

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

To change format, hit FormatCells and make the format change.

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


Gord Dibben MS Excel MVP

On Sun, 21 Dec 2008 05:11:00 -0800, caprey
wrote:

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

How to make identical cell datas active to allow changes
 

What you propose is very powerful.

Even implementing it in VBA completely eliminates the need for a loop.
--
Gary''s Student - gsnu200821


"Gord Dibben" wrote:

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

Select all then EditFind

What: your data

Find All

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

To change format, hit FormatCells and make the format change.

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


Gord Dibben MS Excel MVP

On Sun, 21 Dec 2008 05:11:00 -0800, caprey
wrote:

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

How to make identical cell datas active to allow changes
 

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

Select all then EditFind

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).

--
Rick (MVP - Excel)

How to make identical cell datas active to allow changes
 

Thanks

The recorder makes short work of the operation.


Gord

On Sun, 21 Dec 2008 09:03:01 -0800, Gary''s Student
wrote:

What you propose is very powerful.

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