Conditional formatting of a group of text boxes.

[smar2832]

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 ?

 

[Andrew Poulsom]

What VBA code have you tried exactly? Where are your TextBoxes from?

 

[smar2832]

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.

 

[Andrew Poulsom]

Which line causes the error?

 

[smar2832]

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!

 

[Andrew Poulsom]

You could try putting On Error Resume Next before that line.

 

[smar2832]

worked a treat!

Thanks so much.