+ Reply to Thread
Results 1 to 5 of 5

UNSOLVED - Identify highlighted cells in column, copy cell value to column B of active row

  1. #1
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    381

    UNSOLVED - Identify highlighted cells in column, copy cell value to column B of active row

    Hi,

    I have selected columns B to O on my worksheet and now need a way to loop through the cells and see if:

    1. selected cells are have a color highlight (can be green, yellow or red)
    2. copy the cell value of the highlighted cell to column B of the respective row

    Any suggestions on how I can manage to do this via VBA?

    Thanks,
    A2k
    Last edited by Armitage2k; 11-16-2014 at 01:44 AM.

  2. #2
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Identify highlighted cells in column, copy cell value to column B of active row

    I am playing around with this bit of code here but cant get it to work properly. Not only does it copy empty cells in the respective column but also seems to copy cells that highlighted as "white" (interior.color = 16777215)...

    How can exclude white cells and empty cells in the if statement?

    Please Login or Register  to view this content.
    Thanks,
    A2k

  3. #3
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Identify highlighted cells in column, copy cell value to column B of active row

    OK, I am not getting anywhere...

    I have played arond with other approaches, but I just cannot get vba to correctly identify the color code and then run the macro. Attached a sample and in general what I would need.

    * the attachment is a survey result with all the data displayed in column B. however, the rating section of the survey is formatted differently, showing me the rating scale of 1 to 10 plus "Not sure" and "Did Not Use" in columns B to N.
    * the rated score of the respondent is highlighted in either Red, Yellow, Green or Blue, while all other matrix scores are highlighted in WHITE
    * Now what I would like to have is the respondents score copied over to column B. I tried to approach this by telling VBA to look through columns B:N and copy the cell content of any cell that is NOT WHITE, but that seem to work.
    * another approach I tried is to loop through columns B:N and search for Red, Yellow, Green or Blue highlighted cells, and if any, copy the cell to column B of the active row. No success.

    So, if there is any suggestion from one of the excel masters here, I would greatly appreciate it.

    Thanks,
    A2k

    Survey sample.xlsm
    Last edited by Armitage2k; 11-15-2014 at 02:59 AM.

  4. #4
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Identify highlighted cells in column, copy cell value to column B of active row

    really nobody..? -.-

  5. #5
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: UNSOLVED - Identify highlighted cells in column, copy cell value to column B of active

    Alright, found a solution although its not an elegant one:

    Please Login or Register  to view this content.
    This basically loops through all cells in a certain range and checks if the cell is highlighted in a certain color as defined by RGB code (note that excel doesnt seem to like the numerical index number for colors eg. green is 888631. This is funny since the macro recorder actually records it like that...). For perfectionists, I am sure that the IF statement can be adjusted to automatically check for all three color codes in one line instead of splitting it into three lines.

    Regards,
    A2k

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy active cells from one column as a sequence in another cell
    By adihad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 03:59 AM
  2. Copy a Row to another sheet of a cell in a column is highlighted
    By kdsanderson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:52 AM
  3. [SOLVED] For each row in Column A copy only corresponding Highlighted cells In other Columns
    By boorama in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-17-2013, 02:12 AM
  4. Replies: 0
    Last Post: 04-04-2012, 02:50 PM
  5. Identify active cell and use the column to add formula to another cell
    By JonPugh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-01-2009, 12:32 PM

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