IF statements and row selections

[Navvy]

I finally have a way of permanently highlighting a row if two cells match, even if the workbook is inactive. Here is the code I used (pretty straight forward!):

Private Sub Worksheet_Calculate()
If Range("D1&quot = Range("E5&quot Then
Range("A5:L5&quot.Select
With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With
End If
End Sub


I now need to use several “if” statements to match conditions. Below explains:
My data all starts on row 5. And is labelled A to L.
If column G = “NDT” and the value in H or I equals D1 then do the above in the code.
If column G = “TO” and the value in E equals D1 then do the above code.
If column G = “ROCK” and column F = “X” and then value in I equals D1, then do the above code.
If column G = “ROCK” and column F = “O” and the value in H equals D1, then do the above code.
If column G = “BX” and column F = “X” and the value in H equals D1, then do the above code.
If column G = “BX” and column F = “O” and the value in I equals D1, then do the above code.

A few adjustments need to be made to the above. Instead of selecting A5:L5, I need to select the row which matches the above condition. The worksheet can have numerous rows with data. So the above code only mentions one row not the rest.


thanks

 

[SkipVought]

Hi,

Navvy, YOU NEED TO JOIN Tek-Tips and become a contributing member.

Here's a way...

    Data1 = Cells(1, "D").Value
    
    For lRow = 5 To LastRow
        ColG = Cells(lRow, "G").Value
        ColH = Cells(lRow, "H").Value
        ColI = Cells(lRow, "I").Value
        
        Select Case ColG
            Case "NDT"
                If ColH + Data1 Or ColI = Data1 Then HighlightRow
            Case "TO"
                If ColE = Data1 Then HighlightRow
            Case "ROCK", "BX"
                Select Case ColF
                    Case "X"
                        If ColI = Data1 Then HighlightRow
                    Case "O"
                        If ColH = Data1 Then HighlightRow
                End Select
        End Select
    Next

Skip,
metzgsk@voughtaircraft.com

 

[Navvy]

Amazing as always Skip. As suggested I will be joining the Forum.

Another question, how would I highlight that particular row?

Private Sub Worksheet_Calculate()

Data1 = Cells(1, 4).Value

For lRow = 5 To LastRow
ColE = Cells(lRow, 5).Value
ColG = Cells(lRow, 7).Value
ColH = Cells(lRow, 8).Value
ColI = Cells(lRow, 9).Value

Select Case ColG
Case "NDT"
If ColH = Data1 Or ColI = Data1 Then
Range("A5:L5&quot.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Case "TO"
If ColE = Data1 Then HighlightRow
Case "ROCK", "BX"
Select Case ColF
Case "X"
If ColI = Data1 Then HighlightRow
Case "O"
If ColH = Data1 Then HighlightRow
End Select
End Select
Next


End Sub

I.e. HighlightRow is replaced by
Range("A5:L5&quot.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

But I would like to highlight the particular row it matches rather than A5-L5, so need to highlight A?-L?, where ? is that particular row in the worksheet.

Thanks

 

[SkipVought]

At that point you have the Row, lRow...

   With Rows(lRow)
'here's my format specs
      .Interior.ColorIndex = WhatColor
      .Font.Bold = True
...
   End Row

Skip,
metzgsk@voughtaircraft.com