"Ambiguous name detected: FormatCell"

**copleyr** · 09-04-2009, 06:06 PM

Hello,

I need my macros to search for the word "Cancel" in columns "T". Once found, I need the macros to make that entire row a certain pattern ( .Pattern = xlGray16)

There will be other wording in these cells that contain "Cancel" or "Cancelled".

A compile error keeps occurring. It says "Ambiguous name detected: FormatCell"..

I'm confused with it..... any suggestions?


Sub DoCancelCells()

    Dim strSearchString As String
    Dim wksSheet As Worksheet
    Dim rngSearchRange As Range
    Dim intSearchCount As Integer
   
    Application.ScreenUpdating = False
   
    'String to search for.
    strSearchString = "cancel"
    intSearchCount = 0
   
    For Each wksSheet In ActiveWorkbook.Sheets
   
    wksSheet.Select
   
        Set rngSearchRange = Range("T1:T" & Range("T" & Rows.Count).End(xlUp).Row)
       
            For Each cell In rngSearchRange
           
                If cell.Value = strSearchString Then
               
                    intSearchCount = intSearchCount + 1
                   
                    Call FormatCell(cell.Address)
               
                End If
               
            Next cell
           
            Set rngSearchRange = Nothing
   
    Next wksSheet
   
   
    Application.ScreenUpdating = True
   
End Sub
Sub FormatCell(strCellAddress As String)

    With Range(strCellAddress).EntireRow.Interior
        .Pattern = xlGray16
        .PatternColorIndex = xlAutomatic
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Thank you in advance for your help.

**shg** · 09-04-2009, 06:07 PM

You have two copies of the sub in the module. Delete one.

Or replace all the code with this:

Sub DoCancelCells()
    Const sWhat     As String = "cancel"
    Dim rFind       As Range
    Dim sAdr        As String
    Dim wks         As Worksheet

    For Each wks In ActiveWorkbook.Worksheets
        With wks.Range("T1:T" & wks.Range("T" & Rows.Count).End(xlUp).Row)
            Set rFind = .Find(What:=sWhat, _
                              LookIn:=xlValues, LookAt:=xlWhole, _
                              MatchCase:=False, MatchByte:=False)
            If Not rFind Is Nothing Then
                sAdr = rFind.Address
                Do
                    FormatCell rFind
                    Set rFind = .FindNext(rFind)
                Loop Until rFind.Address = sAdr
            End If
        End With
    Next wks
End Sub

Sub FormatCell(r As Range)
    With r.EntireRow.Interior
        .Pattern = xlGray16
        .PatternColorIndex = xlAutomatic
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

**copleyr** · 09-04-2009, 06:11 PM

Thanks for the same reply. I received the exact same error message.

**shg** · 09-04-2009, 06:13 PM

Search for FormatCell project-wide. There are two (or more) copies. Delete all but one.

**copleyr** · 09-04-2009, 06:16 PM

those were the only two in the project i just Control + F 'd

**shg** · 09-04-2009, 06:17 PM

Post the workbook. You can delete all the data.

"Ambiguous name detected: FormatCell"

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

"Ambiguous name detected: FormatCell"

re: "Ambiguous name detected: FormatCell"

re: "Ambiguous name detected: FormatCell"

re: "Ambiguous name detected: FormatCell"

re: "Ambiguous name detected: FormatCell"

re: "Ambiguous name detected: FormatCell"

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions