Search cell content over multiple worksheets

[medieval]

Hi,

I have an excel spreadsheet with multiple worksheets. By making a cell active in one worksheet, is it possible to show which other cells in all spreadsheets use the active cell as part of any formula or calculation by colouring this say yellow ?

This will help identify any interdependencies.

thank you,
Richard.

 

[SkipVought]

Hi,

Sure, you could loop thru each worksheet in the workbook and assign a color to the interior property of the activecell.

What is your purpose for wanting to do this?

Skip,

_{[red]Be Advised![/red] Dyslexic poets...
write inverse!}

 

[rmikesmith]

Richard,

If I understand you correctly, the following procedure should do what you want. May not be bullet-proof as written but has been tested superficially.

Sub MarkDirectDependents()
Dim Wks As Worksheet
Dim TargetRange As Range
Dim TargetCell As Range
Dim TestCell As Range
Dim CellReference As String
Dim Pos As Long
Dim TotalDependentCells As Long
Dim msg As String


   On Error Resume Next
   Set TargetCell = ActiveCell
   CellReference = TargetCell.Address(External:=True)
   Pos = InStr(1, CellReference, "]", vbBinaryCompare)
   If Pos > 0 Then
     CellReference = "'" & Mid$(CellReference, Pos + 1)
   End If
   
   TotalDependentCells = 0
   For Each Wks In ThisWorkbook.Worksheets
     If Wks.Name <> ActiveSheet.Name Then
     Set TargetRange = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
       If Err.Number = 0 Then
         For Each TestCell In TargetRange
           If InStr(1, TestCell.Formula, CellReference, vbTextCompare) > 0 Then
             TotalDependentCells = TotalDependentCells + 1
             TestCell.Interior.ColorIndex = 6
           End If
         Next TestCell
       End If
     End If
   Next Wks
   
   If TotalDependentCells = 0 Then
     msg = "No other cells reference cell " & ActiveCell.Address
   Else
     msg = "Cell " & ActiveCell.Address & " is referenced by " & TotalDependentCells & " cells."
     msg = msg & vbCrLf & "These cells have had their background color changed to yellow."
   End If
   MsgBox msg, vbInformation + vbOKOnly, "Mark Dependent Cells"
   
End Sub

This should be easier to accomplish; i.e. using the DirectDependent property of the Range object. But this seems to fail for dependent cells on other worksheets.


Regards,
Mike