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

TheGambler

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
May 19, 2017
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Think this will be very difficult to do since excel doesn't have an internal memory of previous values?
 

EFANYoutube

Active Member
Joined
May 19, 2017
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,003
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top