Posts by Gops

    Re: Merge Data Of Same Column From Different Workbooks

    Thanks for the reply. Very close to working. One minor problem. I am getting N/A for Jane and John. Need to retain original vlaue for Jane and John. The value for Jane needs to remain blank and retain the original value for John. It may be a minor adjustment for your formula. I will keep trying. In the meanwhile if you know the answer - much appreciated.

    Re: Merge Data Of Same Column From Different Workbooks

    Quote from Dave Hawley

    I would think a Lookup between the 2 workbooks would do this quite easily.

    Thanks Dave.

    Could you pl. give an example how a Lookup could work in my scenario. NOt sure how to apply the Lookup function to my scenario.


    I searched the site but could not find an answer.

    Here is what I need to do.
    I have two separate workbooks with identical columns. The type of data is student ID, first name, last name, robe id, etc. One workbook is a master list and the other workbook is a secondary list containing some of the students (from the master list). All the students in the master list are eligible to be graduated. However, only those who attended the graduation appear in the secondary workbook (hence they rented the robe). In other words, all students in the secondary workbook will appear in the master workbook but the opposite is not true.

    The column of interest is the one with "robe" heading. In the secondary workbook, all students have the "robe" id filled in (meaning all of them attended graduation and rented a robe). However, in the master workbook, only some students have the "robe" id filled in. It is possible that students who have rented the robe may appear in both the workbooks.

    I would like to merge the "robe" column data from the secondary workbook into the master wrokbook so that the column identifies those who attended the graduation (or rented the robe) and those who did not.

    My apologies if I failed to be much clearer than this. Your help will be much appreciated.

    Re: Cell Color Change If Value Changes

    Quote from Dave Hawley

    I can't fathom out what you want as your Worksheet Change Event code is in Sheet2 and this sheet mirrors Sheet1 via the formulas.

    Try Data Validation to show any error message and Conditional Formatting for the colring

    Hi Dave.

    Thank you for your reply.

    The actual workbook will contain a macro where the user will be adding more sheets that mirrors sheet2. Although I have not shown in this example, the B and E columns already have datavalidation. What I am looking for is to show a reminder if the data entered is different from the previous sheet in the corresponding cells of columns B and E.


    Hello folks.

    Please help me with the code in the attached file.

    The data in sheet1 (named as claim 1) is carried over into sheet 2 (named as claim 2).

    What I am attempting to do is to display a reminder message (and change the cell color to blue) if the name under “position” column (i.e column B) OR the rate in column E is changed or a new one is added (that is different from previous claim) in claim 2 .

    The value could be changed only in B or only in E or both.

    The current code displays the message both times when the change is made in both B and E.

    Could you suggest a modification to the code so that the message appears only once. That is if the value in B is changed first and then E or vice-a-versa, the message should appear only once.


    I have a custom workbook created in Microsoft XL. I would like to make it available to users (interactive) on the web. The users ned to access it and be able to insert data, print it and save to host server. Any directions will be appreciated.

    Thanks in advance.

    Re: Show a message when cell text is changed


    You response has been very helpful. Also, I see a lot of replies, from you, on change detection macro on this forum which leads me to think that you are an expert. Could you pl. comment on my code posted under the heading "change detection in two targets".

    Thank you kindly

    Re: change detection in two targets


    Thnx for the kind reply. clicking on the grey left (you meant left right?)margin or (F9) highlights the the row but F8 did nothing. Not sure what I am doign wrong.


    The code in the file works but, I wonder if you could comment and suggest an improvement to the code in the attached file. With the generous help of good folks on this site, I have developed a code to use in my workbook (a sample is attached). Before I use it I would like to be sure that it will not generate any errors.

    Notes in file give further explanation.

    Also, how do you "step through" to test the code?


    In a range of cells (e.g. B26-B40) I have names (first name and second name or first initial and second name). This data is carried from sheet 1 to the second sheet via formula. Some cells may be blank as well.

    Can you please suggest a VB code to display a message when the user changes the name in any of these cells with data or adds a new name to a cell that is blank, in sheet2

    Thank you.

    Re: Conditional Formatting

    Hey Wigi.

    Your code worked perfectly but needed to further modify to suit my exact need. However, That was nice of you to respond. This site is great. Thanks to all the nice people. Pl see the file for my modified code and notes. I used a combination of cond format and the code to get what I want.

    Many Thanks, again.

    Re: Conditional Formatting

    Quote from Wigi

    Does this page of Chip Pearson help you Gops?

    Together with the Worksheet_Calculate event? See here (also by Chip Pearson).


    Wigi: Yes I did look at that url. But no help. I have restated my need much more clearly than before in the attached file. May be this time some XL Gurus might suggest a code. See both sheet1 and 2. FYI, I have copied the code as in my original worksheet into sheet2.


    Re: Conditional Formatting

    Quote from richardaylin

    Gops - can you attach your file. I suspect that it might only be possible to do this by getting the message to pop up when the same criteria are met as those that turn the font blue ... via a slightly different event macro ... Richard.

    Pl see attached file. The cell E26-E40 look exactly as in my real workbook. FYI, I have also added the VB code that is already in that sheet in my real workbook. I have remed the code to avoid any errors appearing while accessing the sheet. Pl. see notes in the sheet.

    Best regards

    Re: Conditional Formatting


    Many thanks for the reply. Your code works. however, it is the font color not the "fill color" that needs a message response. Changing the "Interior" with "font" in your code did not seem to work. In my worksheet when a user enters a number in range (E26-E40) the font color changes to blue (let us say index numer 5) when a certain condition is met. And when this happen I need to be able to display a message to tell the user to do something.

    Your reply is much appreciated.

    This is probably simple for you folks.

    Using conditional formatting I can change the font color (to blue) of a cell if it meets a certain criteria. Now what I would like is to display a message when the color changes to blue. The cell range is E26 to E40. Any suggestions on a VB code for this.

    Your assistance is much appreciated.

    Re: Advanced Filter

    Quote from royUK


    Why have you started a new Thread here? Please read the rules you agreed to particularly about duplicating Threads. Thanks


    My apologies. My first posting was under a "wrong" heading hence, was not getting any replies. Being desperate to get a response, posted under the "correct" heading.

    Again, my sincere apologies. This site has been very good to me and I intend to honor the protocol/rules.

    This will not happen again.