Conditional Formatting based on a Match value in another sheet

[nishanthc23]

I have 2 sheets, one with Master Data and other is the Layout template. I want to format the Sheet 2 cells to GREEN color based on the Status column value in Sheet 1, if it is equal to 'Available'.
please anyone advise, can I achieve this by VBA in a range on cells in Sheet 2.

Sheet 1 - Master Data

Sheet 2- Layout

 

[bebo021999]

Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub

 

[nishanthc23]

Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub

Where is it checking the condition Status=Available

 

[bebo021999]

Ahh, sorry
If rng = .Cells(i, 1) And .Cells(i, 2)="Available" Then

Sub test()
Dim rng As Range
For Each rng In Sheets("Layout").Range("A1:E2")
With Sheets("sheet1")
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If rng = .Cells(i, 1) And .Cells(i, 2)="Available" Then
rng.Interior.ColorIndex = .Cells(i, 1).Interior.ColorIndex
End If
Next
End With
Next
End Sub