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]

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.

Let's make sure you and I are talking about doing the same thing. This is how I understand it... you have formulas on Sheet1 and and you want to highlight the individual formula cells that reference a cell on "Sheet 3"... is that correct? If not, please clarify.

 

[dvaladas]

Let's make sure you and I are talking about doing the same thing. This is how I understand it... you have formulas on Sheet1 and and you want to highlight the individual formula cells that reference a cell on "Sheet 3"... is that correct? If not, please clarify.

No, I have values on sheet1. And I have formulas on sheet2, sheet3 and sheet4 that are dependents of sheet1. I want highlight the cells on sheet1 that have dependents on sheet3. if a value on sheet1 has a dependent on sheet2 and sheet4 but not in sheet3, should not be highlighted, just if has a dependent on sheet3.

 

[Rick Rothstein]

No, I have values on sheet1. And I have formulas on sheet2, sheet3 and sheet4 that are dependents of sheet1. I want highlight the cells on sheet1 that have dependents on sheet3. if a value on sheet1 has a dependent on sheet2 and sheet4 but not in sheet3, should not be highlighted, just if has a dependent on sheet3.

Ah, I had it backwards. Okay, I think this will do what you want. Make the sheet with the formulas active (Sheet 3 in your case) and make sure the SheetName variable contains the name of the sheet with the values you want to highlight.

[table="width: 500"]
[tr]
	[td][B][COLOR="#00FF00"][COLOR="#008000"]' Run this code from Sheet3... it will color the cells on
' Sheet1 if there is a formula on Sheet3 referencing Sheet1[/COLOR][/COLOR][/B]
Sub HighlightSpecificSheetReferences()
  Dim X As Long, Z As Long, Cell As Range, SheetName As String, Ary() As String
  [B][COLOR="#008000"]' This set the sheets with the values to be colored[/COLOR][/B]
  SheetName = "Sheet1"
  [B][COLOR="#008000"]' Test whether the sheet name need apostrophes around it[/COLOR][/B]
  With Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1)
    .Formula = "='" & SheetName & "'!A1"
    If InStr(.Formula, "'") Then SheetName = "'" & SheetName & "'"
    For Each Cell In Cells.SpecialCells(xlFormulas)
      If InStr(1, Cell.Formula, SheetName & "!", vbTextCompare) Then
        Ary = Split(Cell.Formula, SheetName & "!")
        For X = 1 To UBound(Ary)
          For Z = 1 To Len(Ary(X) & " ")
            If Mid(Ary(X) & " ", Z, 1) Like "[!A-Za-z0-9]" Then
              Sheets(Replace(SheetName, "'", "")).Range(Left(Ary(X), Z - 1)).Interior.Color = vbGreen
              Exit For
            End If
          Next
        Next
      End If
    Next
    .Clear
  End With
End Sub[/td]
[/tr]
[/table]

 

[dvaladas]

Ah, I had it backwards. Okay, I think this will do what you want. Make the sheet with the formulas active (Sheet 3 in your case) and make sure the SheetName variable contains the name of the sheet with the values you want to highlight.

[TABLE="width: 500"]
<tbody>[TR]
[TD][B][COLOR=#00FF00][COLOR=#008000]' Run this code from Sheet3... it will color the cells on
' Sheet1 if there is a formula on Sheet3 referencing Sheet1[/COLOR][/COLOR][/B]
Sub HighlightSpecificSheetReferences()
  Dim X As Long, Z As Long, Cell As Range, SheetName As String, Ary() As String
  [B][COLOR=#008000]' This set the sheets with the values to be colored[/COLOR][/B]
  SheetName = "Sheet1"
  [B][COLOR=#008000]' Test whether the sheet name need apostrophes around it[/COLOR][/B]
  With Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Offset(1)
    .Formula = "='" & SheetName & "'!A1"
    If InStr(.Formula, "'") Then SheetName = "'" & SheetName & "'"
    For Each Cell In Cells.SpecialCells(xlFormulas)
      If InStr(1, Cell.Formula, SheetName & "!", vbTextCompare) Then
        Ary = Split(Cell.Formula, SheetName & "!")
        For X = 1 To UBound(Ary)
          For Z = 1 To Len(Ary(X) & " ")
            If Mid(Ary(X) & " ", Z, 1) Like "[!A-Za-z0-9]" Then
              Sheets(Replace(SheetName, "'", "")).Range(Left(Ary(X), Z - 1)).Interior.Color = vbGreen
              Exit For
            End If
          Next
        Next
      End If
    Next
    .Clear
  End With
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Perfect!

Thank you for the help and patience!