Problem with Nothing command
 

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

Problem with Nothing command
 

On Apr 10, 11:53*am, Wesslan wrote:
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

Problem with Nothing command
 

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!
"Wesslan" wrote:

On Apr 10, 11:53 am, Wesslan wrote:
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