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]...
>
> 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.
>
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=517920
>

[Rick_Stanich]

I simply do not see why the macro doesn't recognize the interior color? Is there more than one method of coloring the interior 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]...
>
> 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?
>
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=517920
>

[Rick_Stanich]

There is no Conditional Formatting?

[Perplexed]

[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]...
>
> 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 how
> I read it).
>
> For Each obj In Worksheets("sheet2").OLEObjects
> If TypeOf obj.Object Is MSForms.TextBox Then
>
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=517920
>

[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]...
>
> 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. )
>
>
> --
> Rick_Stanich
>
> I am me
> ------------------------------------------------------------------------
> Rick_Stanich's Profile:
http://www.excelforum.com/member.php...o&userid=28957
> View this thread: http://www.excelforum.com/showthread...hreadid=517920
>

[Rick_Stanich]

You didn't take my pun seriously?