Depending on the version of FileMaker you are using, you can apply conditional formatting to the text field in order to change the colour instead of changing the format of the data.
All you need to do is select your field in Layout Mode and go 'Format > Conditional...' click Add and for the 'Condition' options select 'Formula is' and input the following for your first criteria:
Self = GetNthRecord ( TABLE::YourField ; Get ( RecordNumber ) - 1 )
And then select magenta from the 'Text Color:' option under 'Format'
Then just repeat the process from 'Add' for the next two, using the following:
1) Self > GetNthRecord ( TABLE::YourField ; Get ( RecordNumber ) - 1 )
2) Self < GetNthRecord ( TABLE::YourField ; Get ( RecordNumber ) - 1 )
And select the appropriate colour for each.
This will then compare it to the record before it in the found set.
If you do want to set the colour of the data, and want to run a script then let me know and I will go through it for you.
I hope this helps
Thank you Orlando. You're a true genius
Your welcome crispylx, glad I could help.
I was wondering if you could help with the next step . I used your code to make a script and its great but a little slow. I'm processing 7000 records and using a loop to visit each record.
Now, if have have 2 fields. The first field is a count and i have 5 records (so they contain 1,2,3,4 & 5) the second is an imported matched value. It might have a value in (for example) record 2 and 4 but will be blank in records 1, 3 & 5. I want to format the field so that if its empty it shows the value form the previous record.
I don't really want to do a looping script throughout as i'll have 200 records but 3000 fields and it may take a while.