Don't know if this is what you want, but it will put the word "fail" in A1 in
red if it appears anywhere in B1:D4.

Sub Fail()
x = "Fail"
With Worksheets(1).Range("$B$1:$D$4")
Set C = .Find(What:=x, MatchCase:=False)
On Error GoTo 0
If Not C Is Nothing Then
FirstAddress = C.Address
End If
If Range(FirstAddress).Value = "Fail" Then
ActiveSheet.Range("$A$1") = "Fail"
ActiveSheet.Range("$A$1").Font.ColorIndex = 3
End If
End With
End Sub

"mjarantilla" wrote:

Ok, here's the deal.

I need to change the color fill of a merged cell (consisting of the first
cells of four adjacent rows merged together) based on the current contents of
any other cell on those four rows.

For example, A1:A4 is the merged cell. B1:B4 are four separate cells, C1:C4
are four separate cells, D1:D4 are four separate cells, etc., etc. The fill
color and text contents of A1:A4 needs to change (pass/fail, green/red)
depending on the content of ANY of the cells in the range B1:D4. If any of
the cells in the range B1:D4 is marked "failed," then the merged cell A1:A4
needs to be marked as "failed," too.

Can anyone help me with some sample code?