Changing font color based on change from previous value

Say I have order sizes streaming in from another program. If the most recent order is less than the previous order, I want the order size to be colored red. If the most recent order is larger than the previous order, I want the order size to be colored blue. If the most recent order is the exact same size as the previous order, I want the order to be colored black.

How would I make code that would let me "remember" the previous order size and the most recent order size, and let me compare them to conditionally format the font?

Hi,

You can add a new sheet which will hold the previous months order.


For example in the current month's order workbook (name "June.xls") add an additional sheet from "may.xls" and name that sheet as "Previous month data".

No you don;t need any macro. Just compare the values using
FORMAT->Conditional Formatting and they key in the formula to compare.
Here you can change the font face,style and what not?

If the answer is not appropriate or your question is different please do revert back.

Regards,
Naras

Hello dgtvr,

I can think of a several ways to track the information. It would help save time if you post the workbook so we can see how the data is laid out and what other functions you may be using on the data. The maximum upload file size is 100 kb.

Sincerely,
Leith Ross

I can't post the exact file, but here is what I'm talking about. I have a value in A1 that keeps changing very quickly and I want to just keep track of whether the change from the previous quantity is positive or negative through a change in the color. I'm not using any function on the workbook but the workbook does recalculate every time the data changes.

Hello dgtvr,

What do these orders look like? Numbers, text, or both?

Thanks,
Leith Ross

just numbers. I would declare as a double.

Hello ,

Here is the macro to change the font color. The code is setup to examine cell $A$1 for a number. This can be changed to another cell or to include more cells if needed.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Hi Leith,

Thank you very much. I've implemented something similar to what you wrote. The problem now is getting the macro to run every time a value changes. I tried using Worksheet_Calculate(), but since the values are changing from a formula, I am not sure how I would get the macro to run. I would like to avoid using an OnTime statement to get the macro to run every second since sometimes the data changes once every few seconds, but sometimes as often as several times per second.

Sincerely,

dgtvr

Hello dgtvr,

If you look back at my previous post, you will see I placed the macro in the Worksheet_Change event. This happens anytime a cell's value is changed by user input or through code.

Sincerely,
Leith Ross

Leith, it seems the only step I missed was saving it. It works now like a charm. Thank you very much.