Request for simple conditional formatting code in VBA

[LRAT]

Hi,

I am looking for a simple block of code that can change the background colour from a cell depending on the value in that cell.

Let me explain:

I want each cell in a range from A1 to A20 on sheet("Worksheet1") change in background colour depending on it's value.

If the value is between 0 and 10 then the background colour needs to change to RGB(255,100,230)
If the value is between 10 and 20 then the background colour needs to change to RGB(100,100,050)
Etc.

The formatting will need to run continuously in the background so that there's no need for an "Update"-button.

It must be straight forward process but after hours of surfing the net I gave up. Hence my question here.

I do not want to use the Conditional Formatting formulas as available in Excel as I want to go for a specialized colour scheme.
Thanks for your help!

Luke

[LRAT]

Further to my original message I would like to add the following:

I am developing a spreadsheet that deals with local weather data.
I want to introduce conditional formatting to each data cell that is in line with the official clourscheme from the Bureau Of meteorology.
So, for the temperature scale (Celsius) the BoM uses following RGB colour schemes:

45+ 201,32,38
42-45 255,85,39
39-42 244,126,32
36-39 253,187,34
33-36 254,217,84
30-33 249,241,137
27-30 220,237,200
24-27 209,226,190
21-24 184,209,164
18-21 158,192,138
15-18 133,175,112
12-15 107,158,86
9-12 76,155,163
6-9 104,174,175
3-6 138,187,191
0-3 92,212,224
-3-0 133,233,232
-6-3 174,234,240
< -6 214,244,247
no data 211,211,211

So, the moment I put in a value for temperature and after I hit "Enter" then I would expect the VBA code to run and change the background colour of the cell to the corresponding RGB-value.
I would like to use the "CASE"-function as this is neat and easy to read.
I need to do this in VBA as it needs to be hard coded. The Conditional Formatting in Excel is too cumbersome, doesn't allow that many parameters and is easily corrupted while applying the copy and paste function.
Any suggestions?
Thanks for your help in advance!
Cheers,

Luke

[Trebor76]

Hi Luke,

Couple of questions:

• Don't the ranges overlap i.e. what RGB would you use if the temperature was 39°C?
• Is the code to run on any cell change or a specific range?

Thanks,

Robert

[LRAT]

Hi Robert,

Thanks for your time.
Yes, there is some overlap. But, basically what it is, is that 38.9C would still be the end of the previous colour and 39.0C would be the start of the new colour.
Temperatures are expressed to 1 decimal accuracy.

To answer your second question:
The table will contain all historical records, whether it's for minimum temperature or maximum rainfall in a period.
So, whenever new data is entered in a data table then Excel should check if this new data breaks a record or not. So, yes, once day when the new data is added then the program should update the table and change the background when required.

I hope this will make sense.
Thanks for your interest and time.
Cheers,

Luke

[Trebor76]

The bands may need tweaking but this event macro on the sheet in question will do the job:

Please Login or Register  to view this content.

Regards,

Robert

[LRAT]

Thanks Robert!
That sounds like a great solution and easy to follow.
I will try it out tomorrow morning and will definitely get back to you.
Thank you very much for all your help (And all that typing)!
Kind regards,

Luke

[Trebor76]

Thanks Luke.

I think the top range should actually be:

Please Login or Register  to view this content.

[LRAT]

Dear Robert,

I ran your code in my spreadsheet and I'm very pleased to let you know that everything works a treat!
It behaves just as expected and I didn't had any bugs to deal with at all.
That is really a fine piece of code and I will keep it in my code snippets library.
Thank you very much for your time and effort! I really appreciate it.
I will close off this topic now and will change the status to "Solved".
Have a great day and thank you very much helping me out!
Kind regards,

Luke

[Trebor76]

Hi Luke,

You're welcome and I'm glad it all worked out. Thanks for marking the thread as solved and the rep

Regards,

Robert