Thanks for all the support the forum has given me so far and sorry for asking so many questions. I am new to the capabilities of Excel/VBA and so far, I'm very impressed. I have another query I was hoping for some help on:
With the help of Dave and Tom, below is a way of permanently highlighting a row if two cells match, even if the workbook is inactive.
Private Sub Worksheet_Calculate()
If Range("D1") = Range("E5") Then
Range("A5:L5").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. I suppose I'll need a row counter or anything else you can suggest.
Thank You.
With the help of Dave and Tom, below is a way of permanently highlighting a row if two cells match, even if the workbook is inactive.
Private Sub Worksheet_Calculate()
If Range("D1") = Range("E5") Then
Range("A5:L5").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. I suppose I'll need a row counter or anything else you can suggest.
Thank You.