Color cells depending on whether odds/values increase or decrease.

[TheGambler]

Hello. I have an excel spreadsheet linked to a betting exchange so that the correct score odds in soccer matches displayed in my worksheet constantly change as the odds on the site change. I want each cell containing the odds for each specific score eg 1 - 0 etc, to highlight in green if the value increases, and to highlight in red if the value decreases. Hence, if the odds for 1 - 0 are 7.4 and increase to 7.6, I would like the cell to highlight in green. If the cell increases yet again to say 7.8, I want it to stay green. However, if instead it went down from 7.6 to 7.4 I would want it to turn red.

Hmm . .I hope that's sufficiently clear? In a nutshell, I want visual alerts for when the correct score odds on the Betting Exchange change.

In the following thread pretty much the same question is asked, and 3 solutions are proposed. However, done of them work. The solution proposed by Antmac throws up a "compile error: Expected Expression" pop-up alert in step 8 (this might be as I was unclear what to paste in on step 6). The solutions proposed by Peter_SSs and Dossfm0q only appear to work if I manually change the values rather than the values updating themselves.

Of course, I appreciate it might not be possible to achieve what I want, but I thought I'd ask the question. I'm afraid I do not have any programming experience, so I would need step by step instructions. Thank you for any help that anyone can provide.

 

[EFANYoutube]

Sounds like you need an "Update" sheet and a "Display" sheet
So have your sheet copy to your "Display" sheet then update the values in the "Update" sheet from the website, Then use conditional formatting on the "Display" sheet to check the diferences and color accordingly

 

[TheGambler]

First off, I have a "Linked to BF" sheet that is directly linked to the betting exchange. That would correspond to your "update" sheet. And I have a "Results" sheets that corresponds to your "display sheet".

Let's say the appropriate cells in my results sheet are simply directly linked to my update sheet (I have some additional calculations, but let's keep it simple!). In which case there won't be any differences.

The alternative is to not have them linked. Just copy the values from update to display sheet so the values in the display sheet don't change as the update sheet changes. So then I can do it so that the cells in the display sheet will change colour when there is a difference. But then the results sheet won't be displaying the correct odds. I could keep copying the odds from the update to the display sheet every time the odds change (which could be every second in the last 5 mins before a match starts!). So that's not really ideal.

 

[EFANYoutube]

I would use a macro and get it to run before the BF sheet is updated, get it to copy the values over to your Results sheet then update the BF so the conditional formatting worked

 

[TheGambler]

I would use a macro and get it to run before the BF sheet is updated, get it to copy the values over to your Results sheet then update the BF so the conditional formatting worked

I only have a very shallow knowledge of macros I'm afraid.

Just to let you know that the BF sheet gets new info and gets "updated" every second (normally the values won't change from 1 sec to the next though). So, anyway, a macro would have to copy the values over every second? In which case the color coding would only last a second?

 

[TheGambler]

Think this will be very difficult to do since excel doesn't have an internal memory of previous values?

 

[EFANYoutube]

Well it does but it takes a bit to set up and you have to have it running all the time for it to retain
Does it need to be every second and if so it looks like you might need a colour coded log where you can select a betting thing(sorry I don't understand betting much) and it would show you the changes for the last hour?
I think the 1 second update will mean you'll have to be glued to the screed to catch changes

 

[TheGambler]

Discussing it with you has triggered a chain of thought and I now realise that I can only get the colouring to work if excel compares with other values that it might differ from. OK, I think I can do a workaround, although not as useful.