compare old value and change background colour of a DDE link and calculated cell

**gpatel** · 03-02-2015, 10:04 AM

Hi,

I have a excel sheet (EXCEL 2013)with calculated fields from input of DDE data and SQL table data (refreshed query every Second)

I want to compare the old value in cell and if higher then current value cell background should be GREEN and if lower then cell background RED.

Sheet1:

B2 is =AMR|Bid+Sheet2!B2
B3 is = AMR|ASK+Sheet2!B3
D2 is =AMR|GB+Sheet2!G4
D3 is =AMR|US+Sheet2!H10

Sheet 2 gets data from SQL table.

then I want to change corresponding cell background to GREEN if lower then old value if the same cell and RED if higher then old Value of the same cell.

I have a timer in module to refresh SQL data every second fetched data is stored in Sheet2.

Please Login or Register  to view this content.

Please help, I have tried multiple solutions but in vain as there is no user entry being done only DDE data and SQL Data.

**gpatel** · 03-03-2015, 01:45 AM

Any help please.

**cruelkix** · 03-03-2015, 02:07 AM

Just copy and paste values from the old data over to another sheet, or hidden cells on the same sheet, right before you call for the new data and then run another macro right after the request for new data comes in to compare the values. Or am I not fully understanding your question?

**gpatel** · 03-03-2015, 04:53 AM

i did the code as below and working fine but with two problems

1. It is Slow
2. It is not working when the Sheet is locked (we have to keep it locked so that the formulas are not changed)

Please Login or Register  to view this content.

can you update it

**cruelkix** · 03-04-2015, 12:06 AM

As far as your two problems go:

1) I'm not sure that application.intersect is a quick method to use. If you can come up with something better is might be a good idea. Without an example worksheet to look at I'm a bit in the dark on what is really going on in your worksheet.

2) When you protect the sheet, you need to check the box that says allow users to format cells. You cannot change cell colors in a protected worksheet/workbook unless you check that box. It's the box right under the first two that are selected by default.

Hope that helps!

**gpatel** · 03-04-2015, 03:10 AM

Attached is the sheet for reference

and thank you for second solution of locking the sheet. I didn't know that macro can change formatting with that checkbox.

But ran into one problem while locking sheet with formatting on. the DDE Links cannot update itself. (check the code in Module sub con refresh with comment Update other link values - V6), any suggestion.

compare old value and change background colour of a DDE link and calculated cell

LinkBack

Thread Tools

Rate This Thread

Display

compare old value and change background colour of a DDE link and calculated cell

Re: DDE input and SQL table input compare cell vaule with old value and colour cell bkgnd

Re: DDE input and SQL table input compare cell vaule with old value and colour cell bkgnd

Re: DDE input and SQL table input compare cell vaule with old value and colour cell bkgnd

Re: DDE input and SQL table input compare cell vaule with old value and colour cell bkgnd

Re: DDE input and SQL table input compare cell vaule with old value and colour cell bkgnd

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Changing the cell colour based on what is input in another cell.

Making cell changed colour, on input.

Cell change colour due to what i input

Change text colour when input cell is less or greater than 1000 to the reference cell

Input Cell to create table with input # of rows

Bookmarks

Bookmarks

Posting Permissions