Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Button Problem | Excel Discussion (Misc queries) | |||
Subtotal command problem | Excel Worksheet Functions | |||
Problem with MID (any text) Command | Excel Worksheet Functions | |||
command bar problem | Excel Programming | |||
command bar problem | Excel Programming |