Macro to conditional format text box colour

[ExcelFailure]

Hello,

I am after a macro to change the colour of a textbox according to whether a cell contains a number that is:

<80 it needs to be red
>90 needs to be green
Anything else then it needs to be orange.

Can anyone help?

[Glenn Kennedy]

Sorry - misread the request. Ignore this post of mine!!

[Philb1]

Try this.
Type a value into A1 to see the box change colour

Please Login or Register  to view this content.

[ExcelFailure]

Thanks for the reply - i get an error on the intersect part of the code.

The code with my small changes is below:

Please Login or Register  to view this content.

[Philb1]

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Is the most likely cause. If not, attach an example

[ExcelFailure]

Same error - just 1 question. Should it be on the code for the sheet where the text box is or where the cell is? (it's two different sheets)

[Philb1]

If it's on 2 sheets then that code won't work.
What's the sheet name or number for the textbox & the cell?
Or post an example to make life easy

[ExcelFailure]

Aha that problem is now sorted thanks. One more thing - at the moment it only changes on the cell change, how can I get it to change on the whole worksheet change?

[Philb1]

By that do you mean any cell on the worksheet? If so this code will do it. You'll have to sort out the finer points as you haven't posted an example

Please Login or Register  to view this content.

[ExcelFailure]

No sorry I just mean I want the macro to run on the change of any cell in the worksheet, not that any cell in the worksheet can be the cell with >80 or <90 etc

[Philb1]

Try this,it colours the textbox when you enter a value in any cell & changes the colour to white if you delete any cell with a value.
It's my last effort today as it's way past my bedtime
Have a play & change things around etc

Please Login or Register  to view this content.