For.... Next Loop? Best way forward?

[smar2832]

Hi VBAers,

As I delve deeper into excel I am learning many more exciting functions but am also coming up against many more problems!


Is there a way to use a "for...next" loop in order to copy the below code 300 times? i.e. I have 300 different text boxes that are conditionally formatted individually based on 300 different cells. If i have a list of the textboxes and a list of the cells could i use a loop to make this happen? or am i best to copy the code 300 times and manually update it?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Sheets("Aust Fixed").Range("B272") > 0 Then
Sheets("Australia").Shapes("TextBox 406").Select
With Selection.Font
.Color = RGB(0, 0, 0)
End With

ElseIf Sheets("Aust Fixed").Range("B272") < 0 Then

Sheets("Australia").Shapes("TextBox 406").Select

With Selection.Font
.Color = RGB(255, 0, 0)
End With

End If

End Sub

Any help would be much appreciated!

 

[smar2832]

No, its called: TextBox 1

no space between Text and Box but a space between Box and 1.

 

[mikerickson]

I'm guessing then that its an ActiveX textbox. My Mac doesn't support ActiveX, so you should look through the Object Browser and find the path of properties of an ActiveX text box that lead to .Font.Color

 

[smar2832]

No its not activex.... its just a textbox from the insert tab...... I think the activex ones dont have any spaces i.e.: textbox1


have you got it working on your mac? would it be possible to post a working example?

thanks heaps BTW

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

works fine. just need to loop....

 

[mikerickson]

Record a macro of you entering some text into the text box.
What code does the macro recorder give you?

 

[smar2832]

weird. Just tried. The Macro does not record anything related to creating a textbox, writing in a textbox, or changing the font of the textbox. the only thing it recorded was me click a cell out of it to deselect it