0
votes

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

1
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. ThanksGeorge
did you read this one? stackoverflow.com/questions/25000691/…krib

1 Answers

0
votes

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