Determining if referenced value is greater then the value cell to the immediate right of OG cell (looped)

  • Hi all.


    First time posting on a forum so apologies if I'm missing something.


    I need to create a VBA formula that prompts the user if a cells value is greater then the value of the cell to the right of it.

    As shown in the attachment, I have a predetermined table (cells with borders) with formulas that are filled based on an exported heat map on the left (no borders). The values in the "value gre" column is determined by the first value in each row of the heatmap that is greater then the value in the AVG*3 column. However there will be an issue if the value is greater then the value in the cell to the immediate right in the heat map. (for example, in row A 221 is in Value GRE, however in the heat map the next value (237) is less then 221).


    I would like to prompt the user to check this table when this occurs, mentioning the cell that it occurs. This should happen for every instance that this occurs.


    So far I have tried to select the value in "column # before" for each row, match the specific column # in the row, offset the cell to the appropriate row, and then see if the value in that cell is greater then the one to the right. Then prompt if it is, and the loop through for each row.


    Does this sound like the proper idea for how the VBA code should be structured? I am still relatively new to VBA.. Please let me know if more info would be helpful!

  • Hello and Welcome to the Forum :)


    To make things a lot easier for anyone willing to give a hand ...


    Why don't you attach a sample Excel file to illustrate both your problem ... and the expected solution ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the welcome and the recommendation. Ive attached the file below.


    So essentially the values generated in column O are determined by the first cell value > 3* the average of the cells "A1-D1" of the heatmap. This then generates a column number to the right of the table based on what column this occurs in. To reduce the error from having two of the same values in the same row (which ironically occurs in the first row here), I figured to use the column # as a reference point, search the column numbers (ie. B2-M2 in the excel file) the offset the row to get to the appropriate cell. I then want to make sure that in the heat map, this value is greater then the next value to the right. If so then great, if not then I want to prompt the user to check the cell that this occurs in. Then repeat for each row of the heat map.


    I still feel like im not really explaining this well so let me know if this helps at all.



    And again, thanks!!

  • My current attempt looks something like this. However I would like to make the Range(B7:M7) variable based on the table that is currently being looked at since I will have multiple heat maps being exported into this file.

  • Hello again,


    If my understanding of your problem is correct ...


    You could have a Conditional Format acting as your Warning Signal ...


    e.g. as soon as the same value appears twice, a color change ...


    Could that help ?

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Something needs to be clarified ...


    If cell N8 =AVERAGE($B$8:$B$11)*3


    Should the same amount appear in all your rows ... or should the average formula adjust itself for each row ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the input.


    Duplicates values are acceptable, however I need to call to the first value of each row in the heatmap greater then a threshold value (3* the average of A1-D1 in the heatmap). The issue arises where if a row has the same value twice (which is shown ironically in the first row of the first table), simply searching for the value might return the wrong cell and thereby negate what I am trying to do.


    My workaround for this is to select the column number of the heatmap where this first value greater then the threshold occurs in each row. Then I would match this to the column number of the heatmap, then offset the activecell by the row # to select the first cell of each with the value *3 the threshold value. Then apply the > formula designed to prompt the user. Then repeat.


    Alternatively if I could use conditional formatting to do this that would be fine, but the logic didnt seem possible when I tried to work through it that way.

  • Same amount.


    For reference this is for a biological experiment. Column one of the heatmap is control (no growth in A1-D1, uninhibited growth in E1-H1). So the threshold is the same for each row of each heatmap (but different on different heatmaps). Each row contains a dilution series of a drug from high concentration to low along the row (A2 highest, A11 lowest). So theoretically, each cell should be lower then the cell to the right. If this is not the case, and the cell is higher then the no growth control average *3, it will cause problems downstream.

  • There are several things which are NOT clear at all ...


    1. Data shown for example in cells B8:M8 : is it a manual input by a user or not ?


    2. Should the check you need be done immediately after each input ? or after input in the last column M ?


    3. What is the cell reference to be used when you need to check the cell input value


    4. Your formula in column N produces the same result for the whole table ... is that right ?


    5. Your array formula in Column M only retrieves the first value of the array of values ... is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for the questions.


    1. This is all imported via macros from another excel sheet. The code grabs columns A-M from the other sheet and pastes it into this file, subsequently providing values for the table formulas.


    2. My idea is to have the prompts occur at the end of everything. Im realizing a better alternative might be to highlight any instances where this "error" occurs and send one prompt that something happened so the user should check the table to see where it occured..


    3. Not sure I understand.


    4. Yes.


    5. Not sure I understand this one, but I'm pretty sure it is correct.

  • Since I still have no idea of the final output you need ;)


    Below a tentative macro to be tested ... :/


    Does it answer your question ....:S


    If not ... feel free to comment


    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • A much simpler way to explain this would be I want the code to go through Column O and determine if the referenced cells are greater then the cell to their immediate right, and if so, to highlight the cell in column O.

  • Did you take a look at the file attached in message # 12 ...???


    Did you run the macro ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Not quite, but some of the code in there will help a lot with what I want to accomplish I suspect. I will try to modify to see if I can make it behave and will post again if I figure it out/reach another block!


    Thanks again so much! I mean it!

  • Glad to hear you are heading in the right direction ...


    Could you add your comments in the test file to make the final output you do need visually explicit ... :)


    Most probably, the macro requires to be adapted ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • A much simpler way to explain this would be I want the code to go through Column O and determine if the referenced cells are greater then the cell to their immediate right, and if so, to highlight the cell in column O.

    Based on your previous explanations ... there is no need to highlight the cell in column O ... since this value is already retrieved from its respective row of data ...


    Below is the Version 2 ... with the check of the cell to the immediate right ...



    Hope this will help

    :)


    P.S. Could you add your comments in the test file to make the final output you do need visually explicit ... :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macro ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi again!


    I have discovered that the macro skill I need work with the most is when to decide when a task actually needs a macro. I was able to figure out my solution with conditional formatting and the offset function. I've attached my solution so you can have an idea of what I was trying to accomplish.


    The edits I made were adding a new function in AA column to mirror the O column, and then next to it in AB a function that (using -14 + the column number the value was found in as a reference guide) spit back the value next to the reference of the O column value. Then I added conditional formatting so that the O column value was highlighted based on whether AA was greater then AB. (I realize this is no longer macro related so apologies if this goes against forum rules lol).

    I will still check out the macro when I have some spare time, and let you know how it works/ if I can get it to accomplish the same task as my solution!


    Thanks again for all the advice and help on the matter!

  • Hello Yulia,


    Very pleased to hear you have managed to design the solution to your problem :)


    Thanks a lot for your Thanks ... AND for the Likes :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)