VBA cell row formatting

[Dave]

I've been struggling with the code for this formatting issue. Any help
would be greatly appreciated.

If a cell (I10) on sheet A is selected as "ECD" then loop through all
the other sheets and look in column E for cells that contain the text
ECD. (It may be in a string, eg "No ECD") When ECD is found, I would
like the entire row to be patterned with xlgrey25 (or some such). I
understand that this should go in the worksheet change event of Sheet
A.

The trick is that in these other sheets the cell that contains ECD are
actually merged of 3 rows.(I know, but I'm stuck with the merge).

HELP!! It's driving me crazy.

Thanks,

Dave

 

[isabelle]

hi Dave,

txt = "DPE"
For Each sh In Worksheets
If sh.Name <> "A" Then
With Sheets(sh.Name).Range("E1:E500")
Set c = .Find(txt, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then firstAddress = c.Address
If Range(firstAddress).MergeCells Then firstAddressM = Range(firstAddress).MergeArea.Address
End With
End If
Next

 

[Dave]

hi Dave,

txt = "DPE"
For Each sh In Worksheets
  If sh.Name <> "A" Then
    With Sheets(sh.Name).Range("E1:E500")
     Set c = .Find(txt, LookIn:=xlValues, LookAt:=xlPart)
     If Not c Is Nothing Then firstAddress = c.Address
     If Range(firstAddress).MergeCells Then firstAddressM = Range(firstAddress).MergeArea.Address
    End With
  End If
Next

Thank you Isabelle for you efforts. It doesn't seem to be working.( I
assumed by DPE you meant ECD). Although it may be a problem with my
workbook that I can't figure out.
Even when I test my workbook by using the "find" function from the
toolbar, all the instances of "ECD" aren't found. I don't get it.

 

[isabelle]

yes, "ECD" or another word
did you try with LookIn:=xlValues and LookAt:=xlPart ?

 

[Dave]

yes, "ECD" or another word
did you try with LookIn:=xlValues and  LookAt:=xlPart ?

--
isabelle

Le 2011-10-05 08:03, Dave a écrit :




- Show quoted text -

Yes, I tried both those.

I don't think the problem lies with your code. It may be with
something else.

Dave

 

[isabelle]

can you put your file here (cjoint.com) and give us the download link so that we can see the problem
--
isabelle



Le 2011-10-05 13:41, Dave a écrit :