Slight Amendment To Current Code

[Dazzawm]

I have the code below that updates sheet 1 with the value in column B on sheet 2 depending on what column I choose. I need a row of code added that whenever a cell is changed then that row gets highlighted. I have highlighted in the code below where I think changes are made on sheet 1 so I think a line of code may need adding there to highlight the row. Thanks

Sub ChangeDescriptionOnSheet1ADToSelectedColumn()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, Rng As Range
Dim LR1 As Long, LR2 As Long
Dim ColLetter As String, FindString As String


' Disable screen updating to reduce flicker
Application.ScreenUpdating = False


' Defines LR1 as the last row of data on Sheet1 based on column A
LR1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
' Defines LR2 as the last row of data on Sheet2 based on column A
LR2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row


' As user to input desired output column
GetColumn:
ColLetter = Application.InputBox("Please enter the desired column letter", "Attention!", Type:=2)
' If the user does not enter a valid column then...
If IsNumeric(ColLetter) Then
    ' Display a message stating they need to try again
    MsgBox "That is not a valid column letter.  Please try again", vbOKOnly, "Attention!"
    ' Go back to the start and request user input a column letter
    GoTo GetColumn
End If


' Sets the check range as AC2 to the last row of AC on Sheet1
Set cRange = Sheets("Sheet1").Range("AD2:AD" & LR1)
' Sets the search range as A2 to the last row of A on Sheet2
Set sRange = Sheets("Sheet2").Range("A2:A" & LR2)


' For each cell in the check range
For Each Cell In cRange
    ' String to find equals cell value
    FindString = Cell.Value
    ' rNumber equals active cell row number
    rNumber = Cell.Row
    ' With the search range
    With sRange
        ' Set Rng as the cell where the value is found
        Set Rng = .Find(What:=FindString, _
                        after:=.Cells(1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False)
            ' If Rng exists then
            If Not Rng Is Nothing Then
                ' Update the specified column of the current row with the value adjacent to Rng
                Sheets("Sheet1").Range(ColLetter & Cell.Row).Value = Rng.Offset(0, 1).Value
            End If
    End With
' Move to next cell in check range
Next Cell


' Re-enable screen updating
Application.ScreenUpdating = True


' Optional message box to confirm all cells have been checked and comments updated if required
MsgBox "Action Completed", vbOKOnly, "Check Complete"


End Sub

 

[Dazzawm]

Any thoughts anyone please?

 

[Dazzawm]

Can anyone help on this please?

 

[Dazzawm]

Can anyone solve this please?

 

[Dazzawm]

I can't find the original thread that this code was on, otherwise I would use that. So if anyone can help I'd be obliged.

 

[AkaTrouble]

if by highlighting you mean colour the cell then maybe this line just after your fill cell line

cell.Interior.ColorIndex = 10

 

[Dazzawm]

if by highlighting you mean colour the cell then maybe this line just after your fill cell line

cell.Interior.ColorIndex = 10

Well I could do with the entire row highlighted in which a cell has changed. Would this line of code go at the end of the line i have highlighted?

 

[AkaTrouble]

this will select the entire row of the active cell place it as its own line after the cell value changed. this method will highlight row only until another cell or sheet is selected wether in macro or by user

activecell.entirerow.select

 

[AkaTrouble]

if you want to colour entire row then this line

Target.EntireRow.Interior.ColorIndex = 5

 

[Dazzawm]

I have put the row of code you suggest as below but I get a run-time error 424 saying object required?

Sheets("Sheet1").Range(ColLetter & Cell.Row).Value = Rng.Offset(0, 1).Value
Target.EntireRow.Interior.ColorIndex = 5