help with VBA to classify and highlight row based on value of cell

[cmccabe1]

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.



'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

[mancubus]

3 threads?

[cmccabe1]

I apologize for that, I am having an issue with internet explorer and have switched over to chrome.

[Bob Phillips]

That is odd, the code in the workbook is nothing like the code that you posted.

[Paul_Hossler]

I was REALLY unsure why you things like this, since it really just seems to kept changing the cells that are selected



'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



Some suggestions using the style (for the most part) that you were using.

No error checking and several key assumptions and not very efficient, but might give you some ideas




Option Explicit
Private Sub CommandButton1_Click()
Dim iGender As Long
Dim iName As Long
Dim iInheritance As Long
Dim iPopFreqMax As Long
Dim iClinvar As Long
Dim iCommon As Long
Dim iClassification As Long
Dim rData As Range
Dim iRow As Long

'set the range
Set rData = Worksheets("annovar").Cells(1, 1).CurrentRegion

'search row 1 for the column number
With Application.WorksheetFunction
iGender = .Match("Gender", rData.Rows(1), 0)
iName = .Match("Name", rData.Rows(1), 0)
iInheritance = .Match("Inheritence", rData.Rows(1), 0)
iPopFreqMax = .Match("PopFreqMax", rData.Rows(1), 0)
iClinvar = .Match("ClinVar", rData.Rows(1), 0)
iCommon = .Match("Common", rData.Rows(1), 0)
iClassification = .Match("Classification", rData.Rows(1), 0)
End With

For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "benign" Then .Cells(iClassification).Value = "benign"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "probable-benign" Then .Cells(iClassification).Value = "likely benign"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "unknown" Then .Cells(iClassification).Value = "uncertain significance"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "untested" Then .Cells(iClassification).Value = "not provided"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "probable-pathogenic" Then .Cells(iClassification).Value = "likely pathogenic"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "pathogenic" Then .Cells(iClassification).Value = "pathogenic"

If .Cells(iClassification).Value = "benign" Then .Cells(iClassification).Interior.ColorIndex = 5 'Blue
If .Cells(iClassification).Value = "likely benign" Then .Cells(iClassification).Interior.ColorIndex = 8 'Cyan
If .Cells(iClassification).Value = "uncertain significance" Then .Cells(iClassification).Interior.ColorIndex = 6 'Yellow
If .Cells(iClassification).Value = "not provided" Then .Cells(iClassification).Interior.ColorIndex = 21 'Purple
If .Cells(iClassification).Value = "likely pathogenic" Then .Cells(iClassification).Interior.ColorIndex = 7 'Magenta
If .Cells(iClassification).Value = "pathogenic" Then .Cells(iClassification).Interior.ColorIndex = 9 'Dark Red
End With
Next iRow
End Sub





BTW, the Col K "pathogenic" desire result was wrong I think since the PopFreqMax = .003 which is less than the threshold of 0.01