VBA cell row formatting

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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
 
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
Click to expand...

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.
 
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 -
Click to expand...

Yes, I tried both those.

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

Dave
 
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 :
 
Back
Top