Excel VBA code to highlight rows for a specific cell

Question

I have created an spreadsheet with 3 sheets:

Sheet1 - Search Sheet2 - Add Data Sheet3 - Database

I want to create a button (Update) to highlight rows (green) for value of cell D4 Sheet1 and highlight rows in Sheet3 (in column 1 i have data which i need to insert in D4 to highlight). I have created one button but everytime just highlight the last row from Sheet3

Private Sub MDAno_Click()
currentrow = Sheet3.Cells(1, 1).CurrentRegion.Rows.Count

If Len(Range("D4")) <> 0 Then

Sheet3.Cells(currentrow, 498) = "Investigating"

End If

End Sub

I have a condition in Sheet3 (if i have "investigating" in column 498 highlight row). Can somebody help me with this ? Thanks

Highlight column A on Sheet3 based on D4 value on Sheet1? If that's your requirement, what your code actually does? — Subodh Tiwari sktneer
Any reason why you would want to VBA? This can be done using conditional formatting. — Nitesh Halai
I need a button because when ID number which is searched in cell D4 to mark as complete or under investigation. I know is possible with conditional formatting. — George
@sktneer My code just highlight the last row of Sheet3, i need to highlight ID from D4 in Sheet3 column A. Thanks — George
did you read this one? stackoverflow.com/questions/25000691/… — krib

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-05-29T13:33:10

Though it can be achieved with a simple conditional formatting, here is what you are trying to achieve...

Private Sub MDAno_Click()
Dim lr As Long, i As Long
Dim ID
lr = Sheet3.Cells(1, 1).CurrentRegion.Rows.Count
ID = Sheet1.Range("D4").Value
If ID <> "" Then
    For i = 2 To lr
        If Sheet3.Range("A" & i) = ID Then
            Sheet3.Cells(i, 498) = "Investigating"
        Else
            Sheet3.Cells(i, 498) = ""
        End If
    Next i
End If
End Sub

Excel VBA code to highlight rows for a specific cell

1 Answers