formula VBA by color

The code below works great until I try to add a formula. Would someone be willing to help out with this as I can not seem to fix it? Thank you.

This is what I am trying to do:
STEP 7: If column J2:J in Low Coverage is Pink then add all and place the value in a new column L on Low Coverage called “Sanger Regions”. If column J2:J in Low Coverage is Yellow then sum of all the yellow and in a new column L on Low Coverage called “New Regions”

Please Login or Register  to view this content.

get an Application-defined or object-defined error and the

Code:
sh3.Range("L2").Formula = "=SUM(J2:J)/.Interior.ColorIndex = 7"
is highlighted. Thanks.

Please Login or Register  to view this content.

Method Range of Object Worksheet failed erorr 1004. Thanks.

I attached the spreadsheet. The seven steps are below and steps 1-3 are working as expected, and steps4-7 are where I need help. I figured it would be easier to look at each step individualy so the formula step is what I attempted (with no luck). The spreadsheet called Low Coverage which is the data as of now and a sheet called desired output which is hopefully, going to be the final. Thank you very much for your help.

STEP 1: Highlight in Red amplicons in Template that do not meet criteria.
STEP 2: If the value in D2:D is “” THEN ignore (If Range("D2:D").value = "" Then)
STEP 3: Match column D2:D in Template if it is highlighted Red with column D2:D in Source.
STEP 4: If there is a match in a new sheet (Low Coverage) all the information in columns A,B,C,D,E,F,G,H,I,J from Source put in there and if it highlighted Red in Template but not in Source
STEP 5: If the D2:D column in Template highlighted Red is not in Source then the information from columns A,B,C,D,E,H,I,J is put in Low Coverage columns A-H.
STEP 6: Match column D2:D in Template if it is highlighted Red with column D2:D in Source AND If in Column J in Source there is a Y then column J in Low Coverage is Pink, if in Column J in Source there is a N then column J in Low Coverage is Y Green, if in Column J in Source there is “” then column J in Low Coverage is Yellow with a ? in it
STEP 7: If column J2:J in Low Coverage is Pink then add all and place the value in a new column L on Low Coverage called “Sanger Regions”. If column J2:J in Low Coverage is Yellow then sum of all the yellow and in a new column L on Low Coverage called “New Regions”.

Thank you for correcting the formula. I have not been able to figure out why only 4 records (not the complete 61) appear in Low Coverage. I am sure I am missing something but do not know what. Thanks.

It's very close, but some of the #'s seem off and it lookslike column I in "Low Coverage" got removed. Thank you.

I must have put the incorrect desired output in there. The Low Coverage is edited with thecorrect values and "?" for the amplicons not in Red. Also, the N should be Y and the Y should be N. The colors are right as is. Thank you.

I will take a look at it tomorrow and get back to you.... have a great Easter and thank you.

It looks like only the first 64 records in template are being used to search. Below are the steps for the desired output. The cells that are highlighted Red in "Template" sh2, are used to search "Source", sh1. There are 61 highlighted Red in Template. I think the calculations are not working because of this. Thank you very much, I hope you had a nice Easter.

STEP 3: Match column D2:D in Template if it is highlighted Red with column D2:D in Source.
STEP 4: If there is a match in a new sheet (Low Coverage) all the information in columns A,B,C,D,E,F,G,H,I,J from Source put in there and if it highlighted Red in Template but not in Source
STEP 5: If the D2:D column in Template highlighted Red is not in Source then the information from columns A,B,C,D,E,H,I,J is put in Low Coverage columns A-H.

Thank you .

This line should color the amplicons not in source turqoise with a ?, is that correct?

Please Login or Register  to view this content.

Thank you for the amazing code , I appreciate all your help.

You were correct and thank you very much. To count the ? the below code doesnt seem to be correct. Thank you again very much .

Please Login or Register  to view this content.

Thank you for all your help!

I removed columns columns 7,8,9 from the Source and the calculations are off. I can not seem to fix it. Thank you.

I figured out the calculation, now I am just trying to figure out why column f and h have values in them in the Low Coverage, but only for those cells that are ? in turqoise. Thanks again for all your help .

Thanks again

Hello RudiS, I wanted to ask if you could tell me if this was possible or not as you were so helpful with my last question.

I am using the multi-step code below and am having trouble on the last few steps. Currently, the VBA will classify, highlight according to the classification, and create 2 new sheets based off of a value. Is there a way to copy and paste specific highlighted rows from the main sheet (annovar), to one off the two newly created sheets? The two new sheets are depended on the value in A2 and get text Known or Unknown put after them. Thank you very much.

The code below runs up until the last step, in an attempt to accomplish the copy and paste (transfer classification) step there is a code, but it doesn't seem to work. Since the new sheets are dependent on the value in A2 I am not sure how to code that. I need some expert help. Thanks .

Copy and paste from annovar to TestName Known:
Dark Red ‘9
Magenta ‘7
Blue ‘5
Cyan ‘8

Copy and paste to TestName Unknown:
Yellow ‘6
Pink ‘22
Purple ‘21

Above is the general and a specific example would be:
In the attached workbook row 4 in annovar is dark red, so the entire row would be copied to TestName Known.
In the attached workbook row 5 in annovar is yellow, so the entire row would be copied to TestName Unknown.

Please Login or Register  to view this content.

Hi,

The newly created worksheets are empty. In the attached workbook I have copied the desired results. Annovar is the master template that contains the highlighted rows to copy to the sheets. I hope this helps and thank you .

The code runs, but the row headers do not transfer.

Application-defined or user-defined error:

ActiveSheet.Range(ActiveSheet.Range("A1").End(xlToRight).Offset(0, 1), ActiveSheet.Rows(1).Cells(Rows(1).Cells.Count)).EntireColumn.Clear (highlighted). Thanks.

Nevermind, I copied the code incorrectly. Thank you very much again for all your help .

I changed the sheet a bit and am having an error that I am having trouble with:

The line that is highlighted is:

Please Login or Register  to view this content.

and the error is Application or Object Defined

I'm not sure how to fix it and instead of using the value in A2, I am using a concatenated value in CA1.

If you run the classify macro and select 1 from the dropdown in A2, you will see the error.

It appears the code is until it tries to sort and clean the data (VB in the calculations). Thank you .

I attached the workbook that I modified the code from.

What is supposed to happen:
1. the data is reformatted (classify macro)
2. a data validation dropdown is put in A2 (classify macro)
3. the values in C2 and B2(Last Name,First Name) are concatenated in cell CA1
4. the inheritance is selected based upon the vlookup
5. a series of classifications highlight the rows in the spreadsheet and populate the classification column based on rules
6. two new sheets are created using the CA1 value (Smith,Mark Known) and Smith,Mark Unknown)
7. the highlighted rows are sorted and transferred to the appropriate sheet
8. if possible a save as prompt asks the user to save the entire workbook to a given directory they browse to.

Steps 1-4 work, it is steps 5-8 that I am not sure about. Thank you

No worries, I hope everything is ok. Is there a way to have the rows highlighted and then sorted based on there classification in the apropriate sheet? The TestBook spreadsheet had that as part of the VBA. Thanks .

Thanks for your help ... I started a new post:

http://www.excelforum.com/excel-prog...ml#post3770387

After the claassify macro is run classification appears in column AT. Thanks again.

Perfect, thank you. I can not figure out why the highlighting in TestBook (attached) is different. In that workbook the coloring was as expected and I do not know what changed. Not every step will have data in it, but depending on the step filled out will ultimately lead to some text in Classification and the row highlighted a particular color. Each step in the code is basically an if statement and if there is no value in it moves to the next statement. In the TestBook this seemed to happen, but since the columns changed it is different. Have a great day and thanks for all your help.

There are 4 steps that will result in text and highlighting in classification:

clinvar
AD or AR inheritance
common
gender

It gets kinda messy and difficult to explain, but I hope this helps.

I have some questions to see if they are possible:

1. Can sheet panel and annovar be removed from the newly saved workbook, but with the calculations still working (as of now w/o panel a #REF error happens)
2. Can the clear new data be disabled on the newly saved workbook
3. Can the data be sized to row 1 on the newly created workbooks
4. Can all highlighting In after AU2 be removed as well as the text in CA1-CF1
5. Can the newly saved workbook have macros disabled or be saved as an xlsx

Thank you .