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.



Private Sub CommandButton1_Click()
Dim Gender As String
Dim Name As String
Dim Inheritance As String
Dim PopFreqMax As Integer
Dim Clinvar As String
Dim Common As String
Dim Classification As String






'STEP 1 - 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")




Range("Clinvar").Select
If ActiveCell.FormulaR1C1 = "unknown" Then
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If


End Sub

[patel]

1) Run your code step py step with F8, you can see that the lines with Cells.Find are useless, you can not have a multi selection.
2) Range("Clinvar").Select , you can use this notation if you assigned the name Clinvar to a specified range
Then your code is totally wrong, you can use this

Private Sub CommandButton1_Click()

LR = Cells(Rows.Count, "E").End(xlUp).Row
For r = 2 To LR
If Cells(r, "E") = "unknown" Then
Rows(r).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
End Sub

[Kenneth Hobs]

For just a few like that, have you considered just adding conditional formatting rules?

Patel, you can do multiple selections but not as shown in the code. You are right in that those finds serve no purpose.

[snb]

Sub M_snb()
For j = 1 To 4
UsedRange.Columns(5).FormatConditions.Add(1, 3, "=""" & Choose(j, "unknown", "pathogenic", "untested", "benign") & Chr(34)).Interior.ThemeColor = 6 + j
Next
End Sub

[cmccabe1]

I am not sure I follow, I am new to VBA, and was trying to use the ClinVar header, (not a column), as this may change locations. Sometimes it might be in column 5 and other times it maybe in column 6. When it finds clinvar, the value in each cell in that column are used to classify and highlight the row. Thank you.

[snb]

Sub M_snb()
y=rows(1).find("ClinVar",,,1).column
For j = 1 To 4
UsedRange.Columns(y).FormatConditions.Add(1, 3, "=""" & Choose(j, "unknown", "pathogenic", "untested", "benign") & Chr(34)).Interior.ThemeColor = 6 + j
Next
End Sub

[cmccabe1]

If the amount of rows is variable, does it matter. Also, is it possible to have the desired output (attached in column K), appear in the Classification column? Thanks.

[Paul_Hossler]

http://www.vbaexpress.com/forum/showthread.php?49562-help-with-VBA-to-classify-and-highlight-row-based-on-value-of-cell

Another reason not to start 2 or 3 threads

Which one are you really interested in??

Also, you say you want to "Highlight Row" but your example only has the K-th cell shaded

[cmccabe1]

I apologize about the multiple posts (internet explorer caused me issues, so I am using Chrome). I attached an example of the desired output in which each row has a classification in it in the Classification column and is highlighted. The classification value depends on the clinvar column in that row. The color is in column H and is only there to show the color of the row.

For example,

In row 2 the clinvar column value is pathogenic so the classification column is pathogenic and the entire row is highlighted dark red.
In row 3 the clinvar column value is unknown so the classification column is uncertain significance and the row is highlighted yellow.

This is just part of a multi-step code designed to reduce the need for the user to manually classify each row (that can be variable and range from 1 to several thousand. Thank you very much.

[snb]

Did you ever try the suggestion in post #6 ?

[Paul_Hossler]

I left the suggestion below a little brute force so that it'd be easy to modify when the rules change

Performance didn't seem to be a consideration, but there's improvement that could easily be made.




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(iClinvar).Value = "pathogenic" Then
.Cells(iClassification).Value = "pathogenic"
.Interior.ColorIndex = 9 'Dark Red

ElseIf .Cells(iClinvar).Value = "unknown" Then
.Cells(iClassification).Value = "uncertain significance"
.Interior.ColorIndex = 6 'Yellow

ElseIf .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 Then

If .Cells(iClinvar).Value = "benign" Then
.Cells(iClassification).Value = "benign"
.Interior.ColorIndex = 5 'Blue
ElseIf .Cells(iClinvar).Value = "probable-benign" Then
.Cells(iClassification).Value = "likely benign"
.Interior.ColorIndex = 8 'Cyan
ElseIf .Cells(iClinvar).Value = "untested" Then
.Cells(iClassification).Value = "not provided"
.Interior.ColorIndex = 21 'Purple
ElseIf .Cells(iClinvar).Value = "probable-pathogenic" Then
.Cells(iClassification).Value = "likely pathogenic"
.Interior.ColorIndex = 7 'Magenta
End If
End If
End With
Next iRow
End Sub

[cmccabe1]

Thank you very much for your help.