Extracting Values from colored cells

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    Hello everyone,

    I am working in a project right now in vba and really need you help

    i have a userform in excel which is prepopulated when user opens a excel file


    Upload Xlsx is the demo excel file in that now i want the Level number Corresponding to red colored cell, what i mean here is cell for Level 6 (L6) is colored red so i want 6 in FirstAnchor Textbox in my userform


    Please help.

    Thankyou in advance

    Files

    • Upload.xlsx

      (971.57 kB, downloaded 69 times, last: )
  • You also have red cells in row 20. Do you want to check only column L for red cells? Could column L have more than one red cell? The file you posted doesn't include your userform. Please post a copy of your file which includes the userform and any macros you are currently using.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • We need to know which column(s) the red cell will be in. We also need to know which riser to look at. Please explain in detail.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • So basically the user will enter the riser number in the user form so i will write a code which will look for that riser number amongst other 20 riser number and calculate what i want to.

    and for the column for the red cell it wont be fixed every time so basically what i want is a loop running in entire worksheet and stops when it detects the red color and give out the level number for that cell

    also you can ignore the riser number thing just assume the worksheet something like in the attachment

  • Code for UserForm2

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • What are the 5 colors and which textbox do you want populated for each color? Can you attach a copy of your file that contains all the colors. Include the userform and macros.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Macro enabled excel sheet is same as above but i am reattaching it here with some updates

    just like in the first one you did red for FirstAnchor

    i want Green For second (RGB 0,255,0) for SecondAnchor,

    Blue (RGB 0,0,255) For ThirdAnchor

    Magenta/purple (RGB 255,0,255) for FourthAnchor

    Yellow (RGB 255,255,0) For FifthAnchor

    Gray (RGB 155,155,155) for SixthAnchor

    Gold (RGB 255,165,0) For seventh Anchor

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hey Mumps can you hep me for the last time please?

    This time in the userform the user enters some Strings in riser number and then what i want that my application to select the rows under that riser number

    Let me give you an example here.

    if the user writes HC-01 in riser number then i want everything under HWS to be stored under variable HWS similarly with CHWS,HWR and CHWR under that particular riser number


    Screenshot(5) shows rows under entered riser number to be selected


    OR if not storing the value, i just want my application to focus on that particular 4 columns and calculate according to that value


    Thanks in advance.

  • It's hard to work with pictures. Please attach a file that includes all your sheets, userforms and macros. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Advance for is the macro userform and upload.xlsx is the sheet

    Now in the userform there is a field for riser number, user enters the riser number. For example the user enters HC-01

    what i want is the application should either save the information below the riser number HC-01 and i want that information for further calculation

    or only select the 4 columns under that particular riser number so that when i take values for futher calculation it takes value from that riser number only.


    For eg: if the user entered HC-01 in riser number then either my application should save all values in the 4 column that is HWS, CHWS, HWR, CHWR in a variable named as hws, chws, hwr chwr OR select/focus on that 4 columns so that if i need some values for further calculation it takes only for that riser number.

  • First of all, I didn't realize that you were working with two files, one containing the macro and the other the data. The original macro I suggested won't work properly because it refers to the workbook containing the macro, not the data workbook. So unless you have changed the macro, it won't work as written. Secondly, do you want the identification of the 4 columns and the further calculations to be done when you click the "Calculate" button on the userform? Also, what further calculations do you want to do?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Yes that's right i want further calculation to be done when clicked on calculate button.

    Further calculation is really though for me to explain still i will try

    in the sheet the red colored cell is 1st anchor point and green colored cell is second anchor point. so basically there will be an expansion loop between these two anchor points so in sheet you see the single blue colored cells in stairs form. they are location for expansion loop.

    so for my calculation what i will do is add floor height given in inches from 1st anchor point to blue colored loop location cell multiply it with constant that is 0.000009 and multiply it with change in temperature.

    and then i will compare the value that comes by multiplying all of these with the value in loop location cell


    I can do all the calculations just confused how to select the particular riser number.


    Thank You for helping me so much tho.