cmccabe1
05-01-2014, 11:00 AM
I am trying to write a multi-step code that will classify based on criteria and highlight a row. The code is below is a small section and it runs, but does not give the desired results that are in column K of the attached spreadsheet. Thanks.
For example,
If ClinVar = "unknown" Then the Classification = "uncertain significance" and that row is highlighted yellow.
[CODE]
'Find columns
Cells.Find("Gender", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Name", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Inheritence", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("PopFreqMax", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("ClinVar", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Common", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Classification", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Dim sh1 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("annovar")
'STEP 2 - check ClinVar
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "benign" Then Classification = "benign"
If Classification = "benign" Then rC.Interior.ColorIndex = 5 'Blue
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "probable-benign" Then Classification = "likely benign"
If Classification = "likely benign" Then rC.Interior.ColorIndex = 8 'Cyan
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "unknown" Then Classification = "uncertain significance"
If Classification = "uncertain significance" Then rC.Interior.ColorIndex = 6 'Yellow
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "untested" Then Classification = "not provided"
If Classification = "not provided" Then rC.Interior.ColorIndex = 21 'Purple
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "probable-pathogenic" Then Classification = "likely pathogenic"
If Classification = "likely pathogenic" Then rC.Interior.ColorIndex = 7 'Magenta
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "pathogenic" Then Classification = "pathogenic"
If Classification = "pathogenic" Then rC.Interior.ColorIndex = 9 'Dark Red
For example,
If ClinVar = "unknown" Then the Classification = "uncertain significance" and that row is highlighted yellow.
[CODE]
'Find columns
Cells.Find("Gender", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Name", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Inheritence", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("PopFreqMax", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("ClinVar", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Common", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Cells.Find("Classification", , xlValues, xlWhole).EntireColumn.SpecialCells(xlCellTypeConstants).Select
Dim sh1 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("annovar")
'STEP 2 - check ClinVar
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "benign" Then Classification = "benign"
If Classification = "benign" Then rC.Interior.ColorIndex = 5 'Blue
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "probable-benign" Then Classification = "likely benign"
If Classification = "likely benign" Then rC.Interior.ColorIndex = 8 'Cyan
If Inheritence = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "unknown" Then Classification = "uncertain significance"
If Classification = "uncertain significance" Then rC.Interior.ColorIndex = 6 'Yellow
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "untested" Then Classification = "not provided"
If Classification = "not provided" Then rC.Interior.ColorIndex = 21 'Purple
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "probable-pathogenic" Then Classification = "likely pathogenic"
If Classification = "likely pathogenic" Then rC.Interior.ColorIndex = 7 'Magenta
If Inheritance = "autosomal dominant" And PopFreqMax >= 0.01 And Clinvar = "pathogenic" Then Classification = "pathogenic"
If Classification = "pathogenic" Then rC.Interior.ColorIndex = 9 'Dark Red