Conditional formatting of a group of text boxes.

smar2832

New Member
Joined
Oct 13, 2011
Messages
17
Hi,

I am using around 200 individual text boxes in a single worksheet that each contain a formula pointing to the value of a unique cell in another worksheet in the same workbook. Each cell being referenced contains a numeric value. I wish to apply conditional formatting to the value in the textbox such that:

If the cell value > 0, use black text.
If the cell value <= 0 use green text.

I have attempted to modify vba code I have found on similar topics without success.

Is anyone able to help ?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

smar2832

New Member
Joined
Oct 13, 2011
Messages
17
Sorry, I probably should have provided that before.

I am using this code:

Dim shp As Shape, tmp


For Each shp In ActiveSheet.Shapes
'only operate on linked shapes
If Len(shp.DrawingObject.Formula) > 0 Then
tmp = shp.TextFrame.Characters.Text
'ignore non-numeric values
If IsNumeric(tmp) And Len(tmp) > 0 Then
shp.TextFrame.Characters.Font.Color = _
IIf(tmp >= 0, vbGreen, vbRed)
End If
End If
Next shp


And I am receiving the following error message:

Run-time error `438':

object doesn't support this property or method.

I have inserted the textboxes using the Textbox icon under the Insert tab in the ribbon.
 
Last edited:

smar2832

New Member
Joined
Oct 13, 2011
Messages
17

ADVERTISEMENT

this one: If Len(shp.DrawingObject.Formula) > 0 Then

I have tested it on a simple spreadsheet and it works until there are certain types shapes present. i.e. other than text boxes! like a line (i have drawings on this spreadsheet as well)

I am pretty sure the non-textbox shapes are causing the error!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,903
Messages
5,834,312
Members
430,276
Latest member
legalcriminal015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top