Need to color fill single rows across multiple sheets

[IHRAcer]

Okay, I've spent way too long on this problem so I'm looking for a little help again, because it's become painfully obvious that I'm an idiot.

I have a workbook that has three sheets. The first sheet contains an ID number in Column A that is duplicated in the other two sheets, also in their respective Column A cells. The first sheet contains basic information for each ID number in each row, the second sheet contains inspection dates and comments, and the third sheet contains testing results. What I need is the ability to turn one row green across all three sheets when I input the completed date in Column F on Sheet 2, or to turn it red if I enter a date in Column H on Sheet 3. My workbook has 700 rows, and I need the ability to turn any of them red or green across all three sheets, depending on the pass or fail dates entered on their respective rows.

I have tried using Conditional Formatting, and have tried pirating macros that I've found by searching online and then adopting them to my sheet, with no luck at all. I don't care which method gets used, as long as it works as intended. But I can tell you now that if someone tells me how to do it using Conditional Formatting, apparently you'll have to spell it out as if I was a small child, because I have failed miserably in my attempts to date.

Any assistance at all will be greatly appreciated, and will halt my inexorable slide into Excel-induced madness.

 

[mumps]

Copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range, ws As Worksheet
    If Target <> "" Then
        Target.EntireRow.Interior.ColorIndex = 4
        For Each ws In Sheets(Array("Sheet1", "Sheet3"))
            Set ID = ws.Range("A:A").Find(Target.Offset(, -5), LookIn:=xlValues, lookat:=xlWhole)
            If Not ID Is Nothing Then
                ws.Rows(ID.Row).EntireRow.Interior.ColorIndex = 4
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub

Next right click the tab name for your Sheet3 and click 'View Code'. Paste the macro below into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range, ws As Worksheet
    If Target <> "" Then
        Target.EntireRow.Interior.ColorIndex = 4
        For Each ws In Sheets(Array("Sheet1", "Sheet2"))
            Set ID = ws.Range("A:A").Find(Target.Offset(, -5), LookIn:=xlValues, lookat:=xlWhole)
            If Not ID Is Nothing Then
                ws.Rows(ID.Row).EntireRow.Interior.ColorIndex = 3
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub

Make your entry into column F or column H and press the RETURN key.

 

[DanteAmor]

Try this:

You must first create named ranges.
- Go to sheet2, select columns A through F and in the name box write "inspection" as shown in the image:

- Go to sheet3, select columns A through H and in the name box write "inspection" as shown in the image:

__________________________________________________________________________
Select Sheet1
1.- On the Home tab, in the Styles group, click Conditional formatting > Manage Rule…
2.- New Rule
3.- Creating a new conditional formatting rule using a formula
4.- In the New Formatting Rule window, select Use a formula to determine which cells to format.
5.- Enter the formula in the corresponding box.

=VLOOKUP($A2,inspection,6,0)<>""

6. - Click the Format… button to choose your custom format. Select green color.
7.- Click OK button
8.- Enter In Aplies to field:

=$2:$1048576

Repeat steps 2 to 8 for the color red and use this formula:

=VLOOKUP($A2,results,8,0)<>""

- Select sheet2 and Repeat steps 1 to 8 for the color red and use this formula:
- Select sheet3 and Repeat steps 1 to 8 for the color red and use this formula:

 

[IHRAcer]

Copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range, ws As Worksheet
    If Target <> "" Then
        Target.EntireRow.Interior.ColorIndex = 4
        For Each ws In Sheets(Array("Sheet1", "Sheet3"))
            Set ID = ws.Range("A:A").Find(Target.Offset(, -5), LookIn:=xlValues, lookat:=xlWhole)
            If Not ID Is Nothing Then
                ws.Rows(ID.Row).EntireRow.Interior.ColorIndex = 4
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub

Next right click the tab name for your Sheet3 and click 'View Code'. Paste the macro below into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range, ws As Worksheet
    If Target <> "" Then
        Target.EntireRow.Interior.ColorIndex = 4
        For Each ws In Sheets(Array("Sheet1", "Sheet2"))
            Set ID = ws.Range("A:A").Find(Target.Offset(, -5), LookIn:=xlValues, lookat:=xlWhole)
            If Not ID Is Nothing Then
                ws.Rows(ID.Row).EntireRow.Interior.ColorIndex = 3
            End If
        Next ws
    End If
    Application.ScreenUpdating = True
End Sub

Make your entry into column F or column H and press the RETURN key.

Thank you very much for the reply, I got pulled away today and didn't get a chance to try it out yet. If I have any questions about it later I'll reply again, but I didn't want to go all weekend without taking the time to thank you for your time!

 

[IHRAcer]

Try this:

You must first create named ranges.
- Go to sheet2, select columns A through F and in the name box write "inspection" as shown in the image:

View attachment 10981

- Go to sheet3, select columns A through H and in the name box write "inspection" as shown in the image:
View attachment 10982
__________________________________________________________________________
Select Sheet1
1.- On the Home tab, in the Styles group, click Conditional formatting > Manage Rule…
2.- New Rule
3.- Creating a new conditional formatting rule using a formula
4.- In the New Formatting Rule window, select Use a formula to determine which cells to format.
5.- Enter the formula in the corresponding box.

=VLOOKUP($A2,inspection,6,0)<>""

6. - Click the Format… button to choose your custom format. Select green color.
7.- Click OK button
8.- Enter In Aplies to field:

=$2:$1048576

Repeat steps 2 to 8 for the color red and use this formula:

=VLOOKUP($A2,results,8,0)<>""

- Select sheet2 and Repeat steps 1 to 8 for the color red and use this formula:
- Select sheet3 and Repeat steps 1 to 8 for the color red and use this formula:

I would like to thank you as well for taking the time to explain the Conditional Format in enough detail that I think I can figure it out if I utilize your method! I got pulled away today and didn't get a chance to try either solution that you guys presented, but didn't want to go all weekend without letting you know that I appreciate your time. Thank you!