Extracting Values from colored cells

**royUK** · Mar 2nd 2021

Halfsparrow

This post has been reported for cross posting. Read the Forum Rules and provide links to all other Forums where this has been posted. Do this before continuing here. You will find that this applies to all Excel Forums

halfsparrow · Mar 2nd 2021

I am sorry i didn't knew i was not allowed to post on different platform.

i will attach all the links here

https://www.mrexcel.com/board/…om-colored-cells.1162862/

https://www.excelforum.com/exc…ed-cells.html#post5478391

https://chandoo.org/forum/thre…lored-cells-in-vba.45865/

https://www.excelguru.ca/forum…d-cells&p=45805#post45805

**royUK** · Mar 2nd 2021

You would if you read the rules and the link in them that explains why we ask you to.

Mumps · Mar 2nd 2021

I'm not sure if I understood correctly what you want to do. However, have a look at the code in the attached file. In the CommandButton1_Click code, I have defined a range (workRange) that corresponds to the columns associated with the riser number. For example, if you enter HC-01, the variable "workRange" will refer to the range E10:H46. I hope this helps.

halfsparrow · Mar 2nd 2021

Hey mumps, i actually want a dynamic application which takes the opened workbook as a reference and not just Upload.xlsx is it possible?

Mumps · Mar 2nd 2021

Will the workbook always have the same name?

halfsparrow · Mar 2nd 2021

No the name of the workbook will be different too

What i was thinking, that when the user opened excel file in 1st step i have made that sheet and workbook as the active workbook and activesheeet

so i will add activeworkbook and activesheet in userform2 instead of using names

Mumps · Mar 2nd 2021

We can make this work without using activeworkbook and activesheet because they can be tricky at times. Will the sheet name always be the same? If so, what is the name? If not, will it always be the first sheet in the workbook? Also, will you have only those two workbooks open when the code runs?

halfsparrow · Mar 2nd 2021

yes the sheet will be always the 1st sheet. but i have figured that out

thankyou

also, when user writes RiserNo it selects the field under that but is it possible that the only that anchor points are displayes that is under that riser number?

what i mean is if the user enters HC-01 it selects the 4 columns under it but can it also select the red colored cell, green colored cell, blue colored cell and display it as firstanachor, secondanchor

Also can i get your email id or something so that i can show you there what i actually want?

Mumps · Mar 2nd 2021

Quote from halfsparrow

Also can i get your email id or something so that i can show you there what i actually want?

Forum rules do not allow communication via email because this does not permit all Forum members to participate. Attach a de-sensitized version of your file and explain exactly what you want.

halfsparrow · Mar 2nd 2021

Okay i will explain you here

Consider the sheet i attached as the same worksheet

now i am working on application which will first ask user to open a excel file they wish to open

once opened another userform will pop up with the fields:

type of pipe : Combobox with 4 options (DLE, DLE-ERV, DSC, DSC-ERV).

Heating and cooling temperature. this will be directly pre-populated from opened excel sheet

Riser number: user will enter the riser number they want to calculate data for

And 7 fields for anchor points which will also be pre populated according to the riser number

NOW HOW MY APPLICATION WILL WORK

Basically it an application for finding the location of an expansion loop in a pipe

so what i will do is user opens excel file from userform1 make it as active workbook and active worksheet extract temperature data from it, user will write riser number and anchor points for that riser number will be pre populated.

then on the backend, when clicked calculate button

i will add the floor heights given in inches between 1st anchor point and 2nd second anchor point, 2nd anchor point and 3rd anchor point, 3rd anchor point and 4th anchor point and so on and store them in different variables. now with the formulae. DeltaL = total height * alpha * change in temperature.

and compare that deltaL with the loop's value

This is what i want to build.

Mumps · Mar 2nd 2021

Have a look at the attached file. I have modified the code a bit. After you enter the riser number, press the RETURN key. Everything seems to be working properly except for the calculations because I really don't understand what the calculations are supposed to do. The code for the calculations would go in the CommandButton1_Click code module. I might be able to help if you could describe step by step, referring to specific cells, rows, columns and sheets how the calculations should work, what the end result of the calculations would be and where you want to put the results. Use the actual values in the "1-2" sheet that you posted to describe the steps.

halfsparrow · Mar 2nd 2021

Mumps thankyou so much it works.

I will work on calculation. If i need something i will let you

Thank-you so much for you help again

Mumps · Mar 2nd 2021

You are very welcome.

halfsparrow · Mar 2nd 2021

Quote from Mumps

You are very welcome.

is it possible that instead of finding the riser number in just activesheet it finds in every sheet present in workbook?

Mumps · Mar 3rd 2021

Will it be in only one sheet or can the number exist in more than one sheet?

halfsparrow · Mar 3rd 2021

It can exist in more than one sheet

Mumps · Mar 3rd 2021

If it can exist in more than one sheet, which values do you want to use to populate the text boxes and combobox?

halfsparrow · Mar 3rd 2021

Quote from Mumps

If it can exist in more than one sheet, which values do you want to use to populate the text boxes and combobox?

Everything remains the same, it is just they can't store all the anchor points in one sheet so they add 2-3 anchor points in one sheet and rest of them in other sheet.

Mumps · Mar 3rd 2021

Try:

Code

Private Sub RiserNo_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    Dim riserNum As Range, srcWB As Workbook, workRange As Range, ws As Worksheet
    Set srcWB = ActiveWorkbook
    For Each ws In srcWB
        With ws
            lRow = .Range("A" & .Rows.Count).End(xlUp).Row
            Set riserNum = .Rows(6).Find(RiserNo.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not riserNum Is Nothing Then
                Set workRange = .Range(.Cells(10, riserNum.Column).Address).Resize(lRow - 9, 4)
                For Each rng In workRange
                    Select Case rng.Interior.Color
                        Case RGB(255, 0, 0)
                            FirstAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(0, 255, 0)
                            SecondAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(0, 0, 255)
                            ThirdAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(255, 0, 255)
                             FourthAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(255, 255, 0)
                            FifthAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(155, 155, 155)
                            SixthAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                        Case RGB(255, 165, 0)
                            SeventhAnchor.Value = Mid(.Range("A" & rng.Row), 2, 99999)
                    End Select
                Next rng
            End If
        End With
    Next ws
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Display More

Extracting Values from colored cells

Participate now!

Share

Tags