Conditional formatting with merged cells

[mjarantilla]

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, D14 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 B14. If any of
the cells in the range B14 is marked "failed," then the merged cell A1:A4
needs to be marked as "failed," too.

Can anyone help me with some sample code?

 

[JLGWhiz]

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 B14.

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]

Thanks. How do I adapt this for multiple entries? So, not just for the first
four rows, but for every four rows after that, too? i.e. Not just A1:A4, but
also A5:A8, A9:A12, etc., all the way down to the 5000s?

(PS: Hmm, it doesn't seem more than once. It works at first, but when I
delete the contents of cell B1 or whatever, it doesn't update A1.)

 

[JLGWhiz]

This wil work but is not perfect. Maybe someone can give you a better
version so you don't have to have the error handler in it.

Sub Fail()
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
x = "Fail"
For n = 1 To LastRow - 3
With Worksheets(1).Range(Cells(n, 2), Cells(n + 3, 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.Cells(n, 1) = "Fail"
ActiveSheet.Cells(n, 1).Font.ColorIndex = 3
End If
End With
n = n + 3
Next n
End Sub