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?

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" 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, 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?

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" wrote:

> 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" 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, 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?

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

"mjarantilla" wrote:

> 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" wrote:
>
> > 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" 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, 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?