Conditional formatting with merged cells
 

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?

Conditional formatting with merged cells
 

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?

Conditional formatting with merged cells
 

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

Conditional formatting with merged cells
 

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