My spreadsheet is monitoring the prices from CoinMarketCap and update dynamically every minute my current portfolio. The idea is to change the color of the particular cell if the value is lower, bigger or equal to the previous status: Equal= yellow, Bigger= green and Lower= red. With the given Excel rules on conditional formatting it not possible to do this. Does somebody can help ? Any existing VBA code available ? or expamles

Thanks in advance for your help :-) Excel 2016 Jacqu

  • For this I can suggest you a solution which comprises VBA & Conditional Formatting. VBA (Macro) will help you to keep track record of Previous value & conditional Format to Format the current value,,, write me are you comfortable with both? – Rajesh S Apr 25 '18 at 9:08
  • Absolutely, unless the cells reacting to the price changes with different colors, I´m happy. The funny thing is, that conditional formatting works only if you type the data manually. For example I also monitoring the % changes on the current portfolio and excel do not support -value- red +value green if you doing dynamic data feed to the cells. Once I use the keyboard, works. Thanks for your help :-) Jacqu – Jacques Pomaski Apr 25 '18 at 14:56

In VB editor Window Double click the Sheet where you are getting Coin Market Updates & Paste this VB script.

I'm assuming that in Column A you are getting the Updates. Replace the column name in line 6 with other if it differs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newVal As Variant

    If Target.Count > 1 Then
        Exit Sub
    End If

    If Intersect(Range("A:A"), Target) Is Nothing Then
        Exit Sub
    End If

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    newVal = Target.Value


    Target.Offset(0, 1).Value = Target.Value

    Target.Value = newVal

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Note, as soon Excel update the Values in Column A you get Previous values in adjacent column.

Please do this,

  • Select either entire Column A or required rows.
  • Click conditional Formatting command.
  • Then hit Highlight Cell Rules.

Then after Format one by one for **Equal to, Greater than & Less than conditions.

Remember, while Format only select Cell B2 for comparison.

enter image description here

  • Thank you very much for the script. For an unknown reason, my cells are all in yellow regardless the value of the cell. I was trying to reach you on email but unfortunately, something is wrong with your server. Do have any alternative email address? – Jacques Pomaski Apr 26 '18 at 12:20
  • Please Try once again the Mail Id it's authentic, otherwise upload the Data on Cloud preferred is Goggle drive. – Rajesh S Apr 27 '18 at 7:06
  • OK, This time email has passed. File attached and email is from Jacques – Jacques Pomaski Apr 27 '18 at 12:57
  • OKay give me some time & I'll return to you soon. – Rajesh S Apr 28 '18 at 5:27
  • Please feel free to answer anytime. – Jacques Pomaski Apr 30 '18 at 23:07

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.