Problem with Nothing command

[Wesslan]

Hi I am struggling with a Nothing command.

If I have a cell that has a dependent, the background colour should
not be changed, whereas the colour should be changed if the cell lacks
dependents.

However, as it is now the macro changes the value irregardless. When
my container takes on a value (i.e is NOT nothing) it should not
proceed with the .interior command, but yet it does. Any good
suggestions?

The code is:

Option Explicit
Sub PaintCellsNotDependent()
'Written by Peder Wessel
Dim Rng As Range
Dim Cell As Range
Dim Container As Variant

'Selects the range to check
Set Rng = Application.InputBox("Select the range to be checked",
"Range", Type:=8)

'If there is no dependent there is an error
On Error Resume Next

'Set Container to nothing to make sure that if it becomes something
there is a dependent
Set Container = Nothing

For Each Cell In Rng
With Cell
Container = .DirectDependents
If Container Is Nothing Then .Interior.Color = RGB(95, 177,
39) 'Sets background color to green if NOT dependet'
Set Container = Nothing
End With
Next Cell

'Resets Error settings
On Error GoTo 0

End Sub

 

[Wesslan]

Hi I am struggling with a Nothing command.

If I have a cell that has a dependent, the background colour should
not be changed, whereas the colour should be changed if the cell lacks
dependents.

However, as it is now the macro changes the value irregardless. When
my container takes on a value (i.e is NOT nothing) it should not
proceed with the .interior command, but yet it does. Any good
suggestions?

The code is:

Option Explicit
Sub PaintCellsNotDependent()
'Written by Peder Wessel
Dim Rng As Range
Dim Cell As Range
Dim Container As Variant

'Selects the range to check
Set Rng = Application.InputBox("Select the range to be checked",
"Range", Type:=8)

'If there is no dependent there is an error
On Error Resume Next

'Set Container to nothing to make sure that if it becomes something
there is a dependent
Set Container = Nothing

For Each Cell In Rng
    With Cell
        Container = .DirectDependents
        If Container Is Nothing Then .Interior.Color = RGB(95, 177,
39)         'Sets background color to green if NOT dependet'
        Set Container = Nothing
    End With
Next Cell

'Resets Error settings
On Error GoTo 0

End Sub

Never mind, I found the solution. I need to set a "set" command in
front of the container = .directdependents
so the code becomes:

Sub PaintCellsNotDependent()
'Written by Peder Wessel
Dim Rng As Range
Dim Cell As Range
Dim Container As Variant

'Selects the range to check
Set Rng = Application.InputBox("Select the range to be checked",
"Range", Type:=8)

'If there is no dependent there is an error
On Error Resume Next

'Set Container to nothing to make sure that if it becomes something
there is a dependent
Set Container = Nothing

For Each Cell In Rng
With Cell
Set Container = .DirectDependents
If Container Is Nothing Then .Interior.Color = RGB(95, 177,
39) 'Sets background color to green if NOT dependet'
Set Container = Nothing
End With
Next Cell

'Resets Error settings
On Error GoTo 0

End Sub

 

[DomThePom]

Yes - the DirectDependants proprty returns a range so to avoid confusion you:

Dim Container As Variant

should have been ~:

Dim Container As range

then it would have been easy to pick up error!