Text Box Conditional Formatting using VBA

I originally posted the following in the General Forum:

I would like to use the 'Conditional Formatting' function on a text box. I would like for the text box on this Sheet2 to change colors when I've typed something into Sheet 1. Can conditional formatting be used on text boxes? If not, is there another way to tell Excel what I am trying to do?

Reply to my question:

teylyn -
'You can't use conditional formatting on a text box. To dynamically manipulate the textbox colors you would need to use VBA.

Start a new thread in the Programming forum if you want to pursue that option.'

New Question:

So I guess I can't use conditional formatting on a text box. Can someone help me with VBA. I've never used this and don't know where to start.

Thanks!

Sorry I don't think I explained myselft well. Basically I'm trying to write a Macro in VBA that will perform the following:

Sheet2 has a textbox with number '1'. Sheet1, Cell A1 has a number '1'. I'm trying to write a Macro that will highlight the textbox on Sheet2 everytime I type 'Yes' into Sheet1, Cell B2.

I hope that makes my problem more clear. I've been learning at writing a Macro in excel but haven't a clue where to start to write something like this.

Any help would be much appreciated. Thank you.

Any ideas? Or just a 'No you can't do that' would suffice. Thanks.

Thanks John.

I copy and pasted the code into VBA and tried running it. I recieved the following message:

Run-time error '438':

Object doesn't support this property or method

I'm not sure what that means or how to fix it. Again, I'm just starting to learn / use VBA so it might be a mistake on my part or a quick fix.

Thanks for the help.

dkgolfer39

Originally Posted by jaslake

Hi dkgolfer39

I'm not certain what you mean by "highlight". This code will change the color of the Textbox, depending on the value of B2.

Please Login or Register  to view this content.

Place the code in Sheet1 (right click Sheet1 tab, select View Code, paste the above code).

Hope this helps.

John

Thanks John.

I tried using the code and I recieved the following message:

Run-time error '438':

Object doesn't support this property or method

I'm not sure what that means or how to fix it. Again, I'm just starting to learn / use VBA so it might be a mistake on my part or a quick fix.

Thanks for the help.

dkgolfer39