Formula to Change Colors If Total Numbers Gain or Lose

[NocSparta]

Hey Excel Forums, your buddy Noc here ready for round two! I am more of an organizing kind of person, so forgive me if these are really basic problems. Anyway, I am trying to get a system where if the number from the "gain/loss" changes, it will turn green if it gains and then red if it loses. So even if it gains for now, it might decrease from the next update. If it's green from an increase, I need it to turn red if it decreases later. Thank you for having me! Edit: Sorry if I was a little redundant haha.

[FDibbins]

If you mean the values in K2:K3, they look like they are manually entered/imported, and not based on any data from your table? (correct me if Im wrong please)

If so, how would excel know what the previous value was, after you have replaced it?

[NocSparta]

Yes, they are entered manually. But that's the thing, I was wondering if there was some way it would detect that the manually inserted number next time was lower or higher than the manual insert before.

[FDibbins]

No, not with any regular formulas, it would hve nothing to compare against. perhaps with VBA

[NocSparta]

What is "VBA?" Edit: Just did some research, scripting.

[JohnTopley]

Select K3 downwards as far as required

HOME ==> Conditional Formatting==> New Rule==>Use a formula ...

In Format values =K3<K2 ... FILL --> RED--> OK

Repeat above

enter =K3>k2 ... FILL ..>GREEN --> OK

Enter data in column K as numbers (123456)

[FDibbins]

John, hopefully you picked up on something I missed, I read it that those values get replaced with each new "session"

[JohnTopley]

Ford,
I may have missed that interpretation: having re-read the original posting it is (to my mind) ambiguous: and only having two entries in the sample would lead to your interpretation. If it is simply two entries only then your feedback is correct with VBA (code) being the only option.

As I pointed out in a previous reply to Tony (Valko) the semantics/ambiguities of (English) language!

John

[NocSparta]

Ford got it right, new "sessions."

[FDibbins]

Could you perhaps start keeping record of your data? In other words, instead of over-writing the "old" data, copy the new data to below it. that way, there would be a reference to work with

[NocSparta]

I'll talk to my friend and get back to you on that.

[NocSparta]

Okay, I'm going to put in some more sample numbers you can work with with a new column to reference from.

[NocSparta]

It's been awhile since I've heard a response, is everything okay?

[JohnTopley]

Try this in CF

=L2>K2 Green

=L2<k2 Red

Apply to L2:L3

[FDibbins]

Perhaps this...
1. highlight the range you want to apply the conditional formatting to (the data in K and L, but avoid using full columns)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =AND(K3="",K2>K1) Format fill GREEN

Repeat 3 and 4 with =AND(K3="",K2<=K1) Format fill RED