UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find Specific Text In Shapes And Change Its Color, Office 2010    
 
   
wornout
post May 24 2020, 11:53 PM
Post#1



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


I have been looking all day and can not seem to find anything to suit
How can I look through all the shapes thats name start with "Task*" and look for the word "Paid" and color Paid to red
Go to the top of the page
 
June7
post May 25 2020, 12:46 AM
Post#2



Posts: 1,538
Joined: 25-January 16
From: The Great Land


You have shapes on Excel worksheet?

Are you using Excel for a database?

Bing: Excel VBA loop shapes

https://www.excelcise.org/vba-loop-through-shapes/

https://www.howtoexcel.org/vba/how-to-loop-...s-in-excel-vba/

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
June7
post May 25 2020, 02:17 AM
Post#3



Posts: 1,538
Joined: 25-January 16
From: The Great Land


Here's what I put together from what I found and using macro recorder:
CODE
Sub loopShapesSheet()
    Dim shp As Shape
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    'If there is any shape on the sheet
    If sh.Shapes.Count > 0 Then
        'Loop through all the shapes on the sheet
        For Each shp In sh.Shapes
            If shp.Name Like "Task*" Then
                If shp.TextFrame.Characters.Text = "Paid" Then
                    shp.TextFrame2.TextRange.Characters.Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
                End If
            End If
        Next shp
    End If
End Sub

Or this version:
CODE
            If shp.Name Like "Task*" Then
                intP = InStr(shp.TextFrame.Characters.Text, "Paid")
                If intP > 0 Then
                    shp.TextFrame2.TextRange.Characters(intP, 4).Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
                End If
            End If

This post has been edited by June7: May 25 2020, 02:29 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
wornout
post May 25 2020, 03:44 AM
Post#4



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


Thank you June7 I had been looking and looking all day the second bit of code worked exactly as I wanted . Do have 1 question as I am putting it in at the end of some other code what do I dim intP as it is asking me to define the variable
cheers.gif hat_tip.gif
This post has been edited by wornout: May 25 2020, 03:52 AM
Go to the top of the page
 
wornout
post May 25 2020, 03:48 AM
Post#5



Posts: 1,360
Joined: 17-November 13
From: Orewa New Zealand


I dim it as long it seems to work
Go to the top of the page
 
June7
post May 25 2020, 11:03 AM
Post#6



Posts: 1,538
Joined: 25-January 16
From: The Great Land


Comparing to number so definitely has to be a number type. I prefixed variable name with "int" so I was thinking Integer. Long works, just occupies a bit more memory space.

This post has been edited by June7: May 25 2020, 11:04 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th July 2020 - 12:33 PM