Conditonal Formatting question

[gally]

Hi was wondering if anyone could help?

I have a cell of data A1 which is linked to an external source and is constantly refreshing. Then in row B I have a macro which is recording the data from cell A1 every second and adding the data to it's rows - for example cell A1 value = 2 this is added to row B1 then the value in cell A1 changes to 2.2 and this is added to B2 one second later and so and so on.

What I wish to do is in another cell C1 this shows the most current value of data recorded so as the rows in B populate it shows the latest value, if the latest time stamped value for example B10 is greater than or less than the previous value in B9 to add some conditional formatting to Cell C1 so the colour of the cell changes if the value is either greater or less than, and then when the new data is added to B11 once again the colour will change in cell C1 if this value is greater than or less than the row before B10 and so and so on, hope that makes sense.

Regards

Gally

[djwaz69]

i would suggest you to upload a similar or the same excel, it would be easy to understand.

[Palmetto]

You will need two conditional formats, using the formula option.

Condition-1: =C1<OFFSET($B$1,COUNT($B:$B)-2,0,1,1) - set the color to, say, red
Condition-2: =C1>OFFSET($B$1,COUNT($B:$B)-2,0,1,1) -set color to green

[gally]

Hi Palmetto - thank you for the reply.

It's on the right track what you suggested but is not working correctly due to this is applying conditional formatting to cell C1 once the values in row B are greater than or less than the value in B1.

The problem I have is with the macro I am running which is populating/recording the data from A1 into row B it's not adding the newest value into B1 and then the older value which was B1 then is moved down into B2 unfortunately it's doing it the other way around which is causing me the headache.

For example cell A1 is recorded and then added into B1 one second later cell A1 new value is recorded and added into B2 then one second later B3 what I need each time is if the newest recorded value in this case B3 is less than or greater than B2 to then apply the conditional formatting to C1 and then if when row is populated again which in case would be B4 if that value is greater than or less than B3 to once again add the relevant conditional formatting to C1.

Once again thank you for you time

Regards
Gally

[gally]

Worked out how to get around it - thank you for taking the time to look

Regards
Gally

[Palmetto]

How about posting your work-around so that others who read this thread will have a possible solution for a similar situation?

[gally]

Hi Palmetto I would quiet happily post the work-around but I ended up changing the way I approached it completely - thus I never actually figured out a work-around as per the criteria above.

I posted solved so as not to waste peoples time trying to figure it out.

Regards
Gally