For Each Loop...... Thoughs?

[smar2832]

Hello,

I am currently struggling to get my first loop working. When i run it i get "Run-time error '1004': Application-defined or object-defined error".

This is the code that I am currently trying to run:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oneAddresses As Range
For Each oneAddress In Worksheets("VBA").Range("B2:B300").Cells
    With Sheets("Aust Fixed").Range(oneAddress.Value)
        ActiveSheet.Shapes(oneAddress.Offset(0, 1).Value).TextFrame.Characters.Font.Color = RGB(IIf(.Value < 0, 255, 0), 0, 0)
    End With
Next oneAddress

End Sub

Any thoughts as to why I would be getting such an error?

I can run this code without the loop as follows and it works fine:

With Sheets("Aust Fixed").Range("B259")
    ActiveSheet.Shapes("TextBox 664").TextFrame.Characters.Font.Color = RGB(IIf(.Value < 0, 255, 0), 0, 0)
End With

Many thanks in advance.

 

[Sal Paradise]

It doesn't look like you're using the right syntax.

1. you are Dim'ing "oneAddresses" and using "oneAddress" in your code.
   (Suggestion: Put "Option Explicit" before your Subs to make sure you need to define all your variables to avoid problems like this)
2. You are using a With without actually using the With

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oneAddress As Range
    Dim oneShape As Shape

    With Sheets("Aust Fixed")
        For Each oneAddress In Worksheets("VBA").Range("B2:B300").Cells
            Set oneShape = .Shapes(oneAddress.Offset(0, 1).Value)
            oneShape.TextFrame.Characters.Font.Color = RGB(IIf(.Value < 0, 255, 0), 0, 0)
        Next oneAddress
    End With

End Sub

 

[mikerickson]

You are planning on looping through 300 shapes, changing shapes font colors on every Selection change ?!?

The thing I notice is in this line.
ActiveSheet.Shapes(oneAddress.Offset(0, 1).Value).TextFrame.Characters.Font.Color = RGB(IIf(.Value < 0, 255, 0), 0, 0)

Do you have the correctly spelt name of a shape in every cell of C2:C300?

Also about the .Value<0 , .Value of what?


Overall, why use Shapes, why not use cells and conditional formatting?

 

[smar2832]

My appologies, I have it working! Thank you so much for your help Mick. I had a few empty cells on the range selection and it was putting it all out!

Thanks again. Perfect.

 

[smar2832]

Thanks Sal,

It's things like your first suggestion that a second set of eyes are great for!

Thanks so much.

It doesn't look like you're using the right syntax.

1. you are Dim'ing "oneAddresses" and using "oneAddress" in your code.
   (Suggestion: Put "Option Explicit" before your Subs to make sure you need to define all your variables to avoid problems like this)
2. You are using a With without actually using the With

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim oneAddress As Range
    Dim oneShape As Shape
 
    With Sheets("Aust Fixed")
        For Each oneAddress In Worksheets("VBA").Range("B2:B300").Cells
            Set oneShape = .Shapes(oneAddress.Offset(0, 1).Value)
            oneShape.TextFrame.Characters.Font.Color = RGB(IIf(.Value < 0, 255, 0), 0, 0)
        Next oneAddress
    End With
 
End Sub