+ Reply to Thread
Results 1 to 9 of 9

Select a cell -> find cell value in workbook then format

  1. #1
    Registered User
    Join Date
    08-08-2012
    Location
    minneapolis
    MS-Off Ver
    Excel 2011
    Posts
    12

    Select a cell -> find cell value in workbook then format

    Hello,

    I am looking to do this:
    1. Select a cell
    2. Run a macro.

    This macro will:
    1. Use that value (of the s to check for the re-occurrence of the value in the current worksheet as well as all other worksheets.
    2. The re-occurrences of the selected cell will need to be filled with a color

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Select a cell -> find cell value in workbook then format

    This isn't clear. Do you have an example?
    Click on star (*) below if this helps

  3. #3
    Forum Contributor
    Join Date
    01-20-2012
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Select a cell -> find cell value in workbook then format

    Try this, tweak how you like it.
    e.g. add some screenupdating true/false etc.

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    08-08-2012
    Location
    minneapolis
    MS-Off Ver
    Excel 2011
    Posts
    12

    Re: Select a cell -> find cell value in workbook then format

    Looks good. I am getting an error on line 10 regarding a Run-time error '1004'.
    the "Select method of Worksheet class failed'

    this line is "wb.select"

    Any idea?

    ---------- Post added at 02:58 PM ---------- Previous post was at 02:53 PM ----------

    I am on 2011 version - MAC

  5. #5
    Forum Contributor
    Join Date
    01-20-2012
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Select a cell -> find cell value in workbook then format

    Got hidden sheets?
    Method doesnt work with hidden sheets, you have to skip those

  6. #6
    Registered User
    Join Date
    08-08-2012
    Location
    minneapolis
    MS-Off Ver
    Excel 2011
    Posts
    12

    Re: Select a cell -> find cell value in workbook then format

    You son of a gun! I just tested it and it appears to be working. I will update in a moment as to whether its application on my BOOK works. Thank you.

    ---------- Post added at 03:34 PM ---------- Previous post was at 03:30 PM ----------

    I need an adaption. If I run the macro on the selection it makes all re-occurrences the given color. Then I move onto another value that I want adapted, and it removes the formatting on the previous cell.

    Also, what is the color code for black text and light green fill?

    ---------- Post added at 03:39 PM ---------- Previous post was at 03:34 PM ----------

    I deleted the "delete all formatting" section. That worked.

    Here is a new issue: I can only add 1 character to blank cells.

    ---------- Post added at 03:43 PM ---------- Previous post was at 03:39 PM ----------

    Character issue solved. Restarted program.

  7. #7
    Forum Contributor
    Join Date
    01-20-2012
    Location
    Amsterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    186

    Re: Select a cell -> find cell value in workbook then format

    Check the code under the comment of delete all formatting. If you take that out you can make seperate button to delete all formatting. As for the color code, record macro and format some colors you want, check the generated code and use it in the macro.

    Sleeping now :d

    iPhone typing is slow (And annoying).... You updated already
    Last edited by rkey; 08-08-2012 at 04:46 PM.

  8. #8
    Registered User
    Join Date
    08-08-2012
    Location
    minneapolis
    MS-Off Ver
    Excel 2011
    Posts
    12

    Re: Select a cell -> find cell value in workbook then format

    Thank you sire. Good evening.

  9. #9
    Registered User
    Join Date
    08-08-2012
    Location
    minneapolis
    MS-Off Ver
    Excel 2011
    Posts
    12

    Re: Select a cell -> find cell value in workbook then format

    One more question: How would I edit the code to allow for multiple (non-consecutive) value selection?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1