RE: Conditional formatting with merged cells
- From: JLGWhiz <JLGWhiz@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 28 Nov 2006 16:33:01 -0800
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?
- Prev by Date: Re: vlookup Variables
- Next by Date: Re: Selecting simple range relative to the active cell.
- Previous by thread: Re: vlookup Variables
- Next by thread: Re: Selecting simple range relative to the active cell.
- Index(es):
Relevant Pages
|
