Highlight range of cells if one cell contains certain data

[bamaisgreat]

I was wandering if the code below could be changed in a way where if the target had G4496-9809 it would still highlight.
Also if it could highlight the entire row instead of just the one cell. Thanks Jamey

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "[COLOR=#FF0000]G4496" T[/COLOR]hen Target.Interior.ColorIndex = 3
If Target.Value = "G4222" Then Target.Interior.ColorIndex = 4
If Target.Value = "G4276" Then Target.Interior.ColorIndex = 6
End Sub

 

[sheetspread]

Is the part that copies to another sheet executed before or after the code that colors?

 

[bamaisgreat]

before
This is the code below that is executed by a button on the 1st sheet

Sub RED_BUTTON()
'
ActiveSheet.Unprotect
'
'
Range("J7:N7").Select
ActiveCell.FormulaR1C1 = "BURN"
ActiveWorkbook.Save


NR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Sheet2").Range("A" & NR).Value = Sheets("BURN").Range("B5").Value
Sheets("Sheet2").Range("B" & NR).Value = Sheets("BURN").Range("J4").Value
Sheets("Sheet2").Range("C" & NR).Value = Sheets("BURN").Range("B7").Value
Sheets("Sheet2").Range("D" & NR).Value = Sheets("BURN").Range("J5").Value
Sheets("Sheet2").Range("E" & NR).Value = Sheets("BURN").Range("B6").Value
Sheets("Sheet2").Range("F" & NR).Value = Sheets("BURN").Range("J6").Value
Sheets("Sheet2").Range("G" & NR).Value = Sheets("BURN").Range("L16").Value
Sheets("Sheet2").Range("H" & NR).Value = Sheets("BURN").Range("C10").Value

Range("P7:R10").Select
ActiveCell.FormulaR1C1 = "BURNEY"
Range("P7:R10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255

Application.Wait Now + TimeSerial(0, 0, 1)

End With
Range("P11:R14").Select
ActiveCell.FormulaR1C1 = "PRINTING"

With Selection.Font
.Name = "Calibri"
.Size = 32
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.ThemeFont = xlThemeFontMinor

Application.Wait Now + TimeSerial(0, 0, 1)

End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255

End With

Range("C10:N10").Select
ActiveWorkbook.Save
Range("P7:R14").Select
Selection.ClearContents

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWorkbook.Save

Range("C10").Select
Selection.ClearContents
Range("J7:N7").Select
Selection.ClearContents

ActiveSheet.Protect


Range("C10").Select
Selection.ClearContents
Range("J7:N7").Select
Selection.ClearContents

ActiveSheet.Protect
End Sub

 

[sheetspread]

I'm assuming this is the part you're referring to:

Sheets("Sheet2").Range("A" & NR).Value = Sheets("BURN").Range("B5").Value
Sheets("Sheet2").Range("B" & NR).Value = Sheets("BURN").Range("J4").Value
Sheets("Sheet2").Range("C" & NR).Value = Sheets("BURN").Range("B7").Value
Sheets("Sheet2").Range("D" & NR).Value = Sheets("BURN").Range("J5").Value
Sheets("Sheet2").Range("E" & NR).Value = Sheets("BURN").Range("B6").Value
Sheets("Sheet2").Range("F" & NR).Value = Sheets("BURN").Range("J6").Value
Sheets("Sheet2").Range("G" & NR).Value = Sheets("BURN").Range("L16").Value
Sheets("Sheet2").Range("H" & NR).Value = Sheets("BURN").Range("C10").Value

which sets just the value, not the formats. So change each line's value sync to a copy/paste operation, e.g:

Sheets("BURN").Range("B5").copy destination:= Sheets("Sheet2").Range("A" & NR)