For, Each, If, Loop?

[Rick_Stanich]

This code runs thru but I am not getting the desired affect, Visible =
false for the condition.
I am checking a Cell for interior color, if the interior color is a
match then I want the coorosponding TextBox set "Visible = False"

Dim obj As OLEObject
Dim i as Long

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
With Worksheets("sheet2")
For i = 1 To 50
..OLEObjects("TextBox" & i).Visible = _
..Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With
End If
Next

p.s.
The initial code was from Bob Phillips, Thanks Bob
But it would not complete.

Dim i As Long
With Worksheets("sheet2")
For i = 1 To 50
..OLEObjects("TextBox" & i).Visible = _
..Range("A" & 10 + 1).Interior.ColorIndex = 3
Next i
End With

So I attempted to fix it.

Any help is appreciated.

 

[Bob Phillips]

I think this is what you want

Dim obj As OLEObject
Dim i As Long

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
i = i + 1
With Worksheets("sheet2")
.OLEObjects("TextBox" & i).Visible = _
.Range("A" & 10 + i).Interior.ColorIndex = 3
End With
End If
Next obj


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich" <[email protected]>
wrote in message
news:[email protected]...

 

[Rick_Stanich]

I simply do not see why the macro doesn't recognize the interior color?
Is there more than one method of coloring th einterior of a cell?

 

[Bob Phillips]

Yes there is, it might be set by conditional formatting, in which case that
is not the interior property.

Go to one of those cells, menu Format>Conditional Formatting, and see if it
has conditions.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich" <[email protected]>
wrote in message
news:[email protected]...

 

[Rick_Stanich]

There is no Conditional Formatting?

Ignore this question if I am way off track.
Is the following code looking for a textbox on the worksheet? (its ho
I read it).

For Each obj In Worksheets("sheet2").OLEObjects
If TypeOf obj.Object Is MSForms.TextBox The

 

[Bob Phillips]

effectively, yes.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Rick_Stanich" <[email protected]>
wrote in message
news:[email protected]...

 

[Rick_Stanich]

Oh a wise guy eh! (Thank you for all your help)

Is Mr. Ogilvy or Mr. Pearson available for input?
(Nothing personal Bob, these two are my Excel Idols, haha; I'm a
groupie In a manly way. )

 

[Bob Phillips]

Ok I'll add you name to the list of posts to bin without reading.

"Rick_Stanich" <[email protected]>
wrote in message
news:[email protected]...

 

[Rick_Stanich]

You didn't take my pun seriously?