Using VB Code to change cell colour on back of formula

Hi

I have a range of cells which populate the words Gold, Silver, Bronze or Red on the back of a LOOKUP formula which all works fine, I now need a VB Code to be able to change the cell colour depending on which word has been returned.

I've attempted to adapt a previously used code however the cells are remaining white and I'm also getting a Runtime Error 13 which appears to be stopping other code within the sheet working as it should.

This is the code I've tried using, can anyone see any obvious errors?

Please Login or Register  to view this content.

Many thanks

Originally Posted by Marcol

Put your code in the worksheet Change module.

Please Login or Register  to view this content.

Can't you just use Conditional Formatting in Range("af4:af18")?

Thanks for your reply

I'm getting the following error when I try using the above code

Compile error: Ambigous name detected: Worksheet_Changne

In relation to the conditional formatting question, I should have said I onlt have access to Office 97-2003 at work which only has the option for 3 rules as you'll know

Cheers

Originally Posted by Marcol

Put the code in the resultant VBa window.
If there is already something in the change event, then add your code to it..

Might be a daft question but at what stage would I add the code? If these were the two codes where would the second code blend with the first and do I need to remove any code for them both to work

Thanks for all your help

Please Login or Register  to view this content.

Hi

I've attached a dummy workbook as requested

I'm almost there with this I think but I'm now getting a runtime error when I enter Gold, Silver etc in column E, the Scores in column AF are changing colour as I want although I also want to be able to see the text in column AF

I've added text into cells E4 to E8 which all works fine, this in turn calculates a percentage score in column AD which then populates a status of either Gold, Silver, Bronze or Red in column AF which is the range of cells that I want to apply the code to change colour depending on the score

Hope that all makes sense

Many thanks in advance for any help

Hi snb

Thanks for the code, it works fine for the first part of what I'm trying to achieve however I also need the cells in column AF to change colour depending on the result of the LOOKUP formula

How would I build on your code to do that please?

Cheers

Originally Posted by protonLeah

If Not Intersect(Target, Range("E4:N18", "Af4:af18")) Is Nothing Then

Hi

I've added the above code where I assume to be correct however the cells in AF4:AF18 remain unchanged.

Can you see what is stopping the cells in AF4:AF18 changing?

Many thanks

Please Login or Register  to view this content.

[QUOTE=Leith Ross;2389268]Hello Twaddy,

NOTE: When a formula calculates, it does not trigger the Worksheet_Change event.

QUOTE]

Hi Leith

Thanks for your reply

So to clarify, it isn't possible to have a change event change the cell colour in e4:n18 based on what is typed into the cell AND that changes af4:af18 automatically depending on the outcome of the LOOKUP formula?

Many thanks

Twaddy

Hi

Thanks very much for all your help and advise, as you can tell my knowledge around VB is almost non existent so your time is much appreciated.

Looks like I'll have to look for an alternative solution, like getting my company to upgrade Office!

Cheers

Hi Twaddy

It has been suggested several times in the replies to your thread, that you should post a sample workbook showing what you have so far.

There is little or no reason why most of the replies you have recieved should not solve your problem, so I can only conclude that your actual workbook is a bit more involved than you are admitting.

If you post a desensitised workbook then, just perhaps, we could not only solve your problem and save your company contributing to Mr William Henry "Bill" Gates IIIs' personal wealth and hey! your boss might even throw a token of gratitude towards yours.....

Originally Posted by Marcol

Hi Twaddy

It has been suggested several times in the replies to your thread, that you should post a sample workbook showing what you have so far.

There is little or no reason why most of the replies you have recieved should not solve your problem, so I can only conclude that your actual workbook is a bit more involved than you are admitting.

If you post a desensitised workbook then, just perhaps, we could not only solve your problem and save your company contributing to Mr William Henry "Bill" Gates IIIs' personal wealth and hey! your boss might even throw a token of gratitude towards yours.....

Hi

I was only asked once to my knowledge to post a dummy workbook which I did in post No 8, perhaps I didn't post correctly or perhaps you missed it.

My understanding from the replies are that a worksheet change event can not run alongside a worksheet calculate and also that a formula cannot trigger a worksheet change event.

If I've misunderstood then I apologise.

I really do appreciate all the help I've received

Thanks

Originally Posted by Leith Ross

Hello Twaddy,

The Worksheet_Change event is fired when either the user types in the cell and hits return or a user makes a selection from a Validation drop down list.

However, when a cell's content is changed by a Formula, a cell link, a DDE conversation, a Forms drop down, or an ActiveX drop down then the event is NOT fired.

Hi Leith

Thanks for taking the time to look at my litle problem, much appreciated.

Something which I don't think I mentioned on previous posts or on the workbook was the bandings for differing grades in column AF which are as follows

Red - 0% to 24%
Bronze - 25% - 62%
Silver 63% - 79%
Gold 80% - 100%

I've tweaked the formula in AF to reflect this as there isn't a banding for Amber. The formula appears to work ok and the correct grades populate depending on the score however I'm not sure if by doing so it's had a knock on effect to the code as the cell colours in AF aren't formatting correctly based on the text

Would you mind taking a look to see if you can spot anything obvious?

Thanks again

I've attached an updated version of the worksheet with the amended formula