VBA to format cell if cell has dependents

[rupertlo]

(Apologies if this has already been answered, I have search the forum but cannot find an exact answer)

I am looking for VBA which will analyse a cell and format it if it has dependendents within the same Workbook, dependents will not be in the same worksheet as the cell.

In layman terms:

If selected cell has dependents then format cells in a certain way
else nothing


My knowledge of VBA is pretty rudimentary but I can all of it apart from getting VBA to return a positive value to the If in the event that the selected cell has any dependents.

Many thanks.

 

[Rick Rothstein]

...and format it if it has dependendents within the same Workbook...

I'm about to go to sleep, so I won't be able to look at this until I get up, but I have a question... does the above quote mean some dependents can be in other workbooks?

 

[rupertlo]

No all in the same Workbook but definitely not on the same worksheet.

Thank you.

 

[Rick Rothstein]

See if this helps....

Here is some code I have posted in the past, modified for your condition, which will color the cells red. However, note that these cells are only the ones in the UsedRange for the ActiveSheet. So if the used range was A1:F9 and you had a formula somewhere that referenced A1:A100, only A1:A9 will get colored in. It would take what seems like forever to search every cell on the worksheet looking to see if it is a dependent cell or not.

Sub ColorDependentCellsOnActiveSheetRed() 
  Dim ShapeCount As Long, R As Range, DependantCells As Range 
  Application.ScreenUpdating = False 
  ActiveSheet.ClearArrows 
  ShapeCount = ActiveSheet.Shapes.Count 
  For Each R In ActiveSheet.UsedRange 
    R.ShowDependents 
    If ActiveSheet.Shapes.Count > ShapeCount Then 
      If DependantCells Is Nothing Then 
        Set DependantCells = R 
      Else 
        Set DependantCells = Union(R, DependantCells) 
      End If 
    End If 
    ActiveSheet.ClearArrows 
  Next 
  DependantCells.Interior.ColorIndex = 3 
  Application.ScreenUpdating = True 
End Sub

 

[rupertlo]

Apologies for the delay in replying - that worked perfectly.

Thank you for taking the time to help me.

Regards,

Rupert.

See if this helps....

Here is some code I have posted in the past, modified for your condition, which will color the cells red. However, note that these cells are only the ones in the UsedRange for the ActiveSheet. So if the used range was A1:F9 and you had a formula somewhere that referenced A1:A100, only A1:A9 will get colored in. It would take what seems like forever to search every cell on the worksheet looking to see if it is a dependent cell or not.

Sub ColorDependentCellsOnActiveSheetRed() 
  Dim ShapeCount As Long, R As Range, DependantCells As Range 
  Application.ScreenUpdating = False 
  ActiveSheet.ClearArrows 
  ShapeCount = ActiveSheet.Shapes.Count 
  For Each R In ActiveSheet.UsedRange 
    R.ShowDependents 
    If ActiveSheet.Shapes.Count > ShapeCount Then 
      If DependantCells Is Nothing Then 
        Set DependantCells = R 
      Else 
        Set DependantCells = Union(R, DependantCells) 
      End If 
    End If 
    ActiveSheet.ClearArrows 
  Next 
  DependantCells.Interior.ColorIndex = 3 
  Application.ScreenUpdating = True 
End Sub

 

[dvaladas]

See if this helps....

Here is some code I have posted in the past, modified for your condition, which will color the cells red. However, note that these cells are only the ones in the UsedRange for the ActiveSheet. So if the used range was A1:F9 and you had a formula somewhere that referenced A1:A100, only A1:A9 will get colored in. It would take what seems like forever to search every cell on the worksheet looking to see if it is a dependent cell or not.

Sub ColorDependentCellsOnActiveSheetRed() 
  Dim ShapeCount As Long, R As Range, DependantCells As Range 
  Application.ScreenUpdating = False 
  ActiveSheet.ClearArrows 
  ShapeCount = ActiveSheet.Shapes.Count 
  For Each R In ActiveSheet.UsedRange 
    R.ShowDependents 
    If ActiveSheet.Shapes.Count > ShapeCount Then 
      If DependantCells Is Nothing Then 
        Set DependantCells = R 
      Else 
        Set DependantCells = Union(R, DependantCells) 
      End If 
    End If 
    ActiveSheet.ClearArrows 
  Next 
  DependantCells.Interior.ColorIndex = 3 
  Application.ScreenUpdating = True 
End Sub

Rick,

Is there any chance to adapt the code to see dependant cells on a specific sheet? For example, I want to highlight all the cells on sheet 1 that have dependant cells but only on sheet 3.

Thanks in advance

 

[Rick Rothstein]

Rick,

Is there any chance to adapt the code to see dependant cells on a specific sheet? For example, I want to highlight all the cells on sheet 1 that have dependant cells but only on sheet 3.

That would be cumbersome as you would need to add a loop that navigated each arrow to the dependent sheet, test the parent cell's name to see if it was the correct sheet, note it in a boolean variable if it was, return to the main sheet, check the note to see if it is True and color the cell if it was, reset the boolean variable to False and iterate the loop's next iteration. Doable, but cumbersome. Alternately, though, I think we can examine the cell's formula looking for the sheet reference directly and do it that way. Give this a try...

[table="width: 500"]
[tr]
	[td]Sub HighlightSpecificSheetReferences()
  Dim Cell As Range, SheetName As String
  SheetName = "Sheet3"
  [COLOR="#008000"]' Test whether the sheet name need apostrophes around it[/COLOR]
  With Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1)
    .Formula = "='" & SheetName & "'!A1"
    If InStr(.Formula, "'") Then SheetName = "'" & SheetName & "'"
    [COLOR="#008000"]' Check each formula cell for the sheet name and color the cell[/COLOR]
    For Each Cell In Cells.SpecialCells(xlFormulas)
      If InStr(1, Cell.Formula, SheetName & "!", vbTextCompare) Then Cell.Interior.Color = vbRed
    Next
    .Clear
  End With
End Sub[/td]
[/tr]
[/table]

 

[dvaladas]

That would be cumbersome as you would need to add a loop that navigated each arrow to the dependent sheet, test the parent cell's name to see if it was the correct sheet, note it in a boolean variable if it was, return to the main sheet, check the note to see if it is True and color the cell if it was, reset the boolean variable to False and iterate the loop's next iteration. Doable, but cumbersome. Alternately, though, I think we can examine the cell's formula looking for the sheet reference directly and do it that way. Give this a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub HighlightSpecificSheetReferences()
  Dim Cell As Range, SheetName As String
  SheetName = "Sheet3"
  [COLOR=#008000]' Test whether the sheet name need apostrophes around it[/COLOR]
  With Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1)
    .Formula = "='" & SheetName & "'!A1"
    If InStr(.Formula, "'") Then SheetName = "'" & SheetName & "'"
    [COLOR=#008000]' Check each formula cell for the sheet name and color the cell[/COLOR]
    For Each Cell In Cells.SpecialCells(xlFormulas)
      If InStr(1, Cell.Formula, SheetName & "!", vbTextCompare) Then Cell.Interior.Color = vbRed
    Next
    .Clear
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Sorry for the late reply. Nithing happens. Don't return any error but nothing happens. If it helps, I have the button with the code on sheet 1..

 

[Rick Rothstein]

Sorry for the late reply. Nithing happens. Don't return any error but nothing happens. If it helps, I have the button with the code on sheet 1..

You have to run the code with the sheet having the formulas as the active sheet. Inside the code, I have this line of code which controls the sheet name you want to look for inside the formulas on the active sheet...

SheetName = "Sheet3"

You need to change this sheet name as needed.

Edit Note: I used "Sheet3" but I now see you said the sheet was named "Sheet 3" with a space before the number... if you make that change to the indicated line of code, I think you will get the results you want.

 

[dvaladas]

You have to run the code with the sheet having the formulas as the active sheet. Inside the code, I have this line of code which controls the sheet name you want to look for inside the formulas on the active sheet...

SheetName = "Sheet3"

You need to change this sheet name as needed.

Edit Note: I used "Sheet3" but I now see you said the sheet was named "Sheet 3" with a space before the number... if you make that change to the indicated line of code, I think you will get the results you want.

So, i tried put the button in sheet3 and change the SheetName to "Sheet1" and it works, all the cells that have dependents turned green, but on sheet 3. What I need is that the cells that turns green are the ones on sheet1. Maybe I not have expressed myself very well, thanks for being help me.