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 ?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

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