Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i make a chart follow the active cell | Charts and Charting in Excel | |||
make one cell identical to another | Excel Worksheet Functions | |||
Looking Up Datas when Key Values are Identical | Excel Worksheet Functions | |||
How do I make it so that the active cell follows the cursor keys? | Excel Discussion (Misc queries) | |||
How do I make the border of an active cell in Excel darker? | Excel Discussion (Misc queries) |