Extracting Values from colored cells

Quote from Mumps

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

It shows object doesn't support this property.

Quote from Mumps

Which line of code is highlighted when you click "Debug"? Also, when you respond, please don't paste anything from the previous post. This avoids clutter.

It fine i figured it out. Thakyou

It was just " For each ws in ActiveWorkBook.Worksheets" instead of "For each ws in ActiveWorkBook(srcWB)"

Thank-You

Quote from Mumps

My pleasure.

Mumps i need you again, so sorry i keep asking your help but i need this project to be done in few days.

Actually now i want to add the floor heights in column C from 1st anchor to blue colored single cell (That is loop location) in each HWS CHWS HWR and CHWR and store it in variable with same name that is hws chws hwr and chwr

it should be done when clicked on calculate button.

Can you please help me with this?

Thank-You

Quote from Mumps

Please attach updated versions of both files. Make sure they include all the user forms and macros including any code that you have added.

Also please explain what you want to do as above with an example from your data using specific cell addresses .

Final.xlsm is the macro userform and uload(1).xlsx is the excel file

Now i am creating another sub when the calculate button is clicked

in that i need the addition of floor heights given in inches from first anchor point to the light blue colored single cell for HWS. same thing for CHWS HWR and CHWR

So for example for HWS i need 116.25 + 116.25 +116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25

For CHWS i need 116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25

and save the result in varible named 1hws, 1chws, 1 hwr, 1chwr

variable has 1 because i will need to save this data for 2nd anchor point, 3rd anchor point and so on.

Thank- you

Quote from Mumps

I think I understand how you got:

They are the values for C28:C35 and that total would be saved in the variable 1hws for the first anchor. Is this correct? How would you calculate the total for 2hws which would be for the second anchor?

Since the anchor points can be in different sheets, how do you want that to be handled?

I have the anchor point location saved in the textbox, maybe i can use that as a reference and add the values.

Quote from Mumps

You can give that a try.

Can you help me with that if you can please?

With workRange



Dim sumVal12 As Double



With ws

sumVal12 = Application.Sum(.Range(.Cells(FirstAnchor, 3), .Cells(SecondAnchor, 3)))



MsgBox (sumVal1 - 2)



End With





End With

I have tried this but i cannot reference the variables in another subprocedure in this sub procedure

Quote from Mumps

The first anchor point is in cell E28 (red). This cell is above the light blue cell in column E which is in cell E36. The total for the first anchor is calculated by adding the values in C28:C35. The second anchor is in E40 (green). What range of values in column C would you add to get the total for the second anchor? The third anchor is in E43 (dark blue). What range of values in column C would you add to get the total for the third anchor?

In this particular case it does not have loop after 2nd and 3rd anchor so it can ignore and do nothing if it does not find the light blue colored cell

No i mean it should look for the anchor point in the riser number that user has entered and look for the blue colored cell if it is present it should add the floor height from the anchor point to the blue colored cell. and about the blue colored cell in different sheet, yes it would be present but it will always have a anchor point above it

Quote from Mumps

Are you saying that the code should look only for anchors that are above the light blue colored cells and ignore the anchors below? Since the anchors can be in more than one sheet, will there be light blue colored cells in any of the other sheets?

No i mean it should look for the anchor point in the riser number that user has entered and look for the blue colored cell if it is present it should add the floor height from the anchor point to the blue colored cell. and about the blue colored cell in different sheet, yes it would be present but it will always have a anchor point above it

Can you please help me with this?

Quote from Mumps

Are you saying that the code should look only for anchors that are above the light blue colored cells and ignore the anchors below? Since the anchors can be in more than one sheet, will there be light blue colored cells in any of the other sheets?

We could also just take the anchor points value from the text box and add the values till lightblue colored cell and if it finds Green color before the light blue color it will just do nothing and return no loop found. (For 1st anchor)

Similarly can code for second anchor.

I really need your help with this.

Thank you

Quote from Mumps

I have managed to add the values till lightblue colored cell for the first anchor point which is red. The difficulty I am having is adding the values for the other anchor points if they are in a different sheet.

Dont worry i will figure that out,

can you please send me whatever you have?

Thankyou mumps, thank-you so much