INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Single cell colour change depending on condition

Single cell colour change depending on condition

(OP)
All,

I have a grid containing rows of table records.

I use the following code within the Object: Grid1

Procedure: AfterRowColChange

CODE -->

LPARAMETERS nColIndex
grno = RECNO()

thisform.grid1.Refresh 

Procedure: Init

CODE -->

PUBLIC grno
grno = RECNO()

this.setall("Dynamicbackcolor", ;
  "IIF(RECNO()=grno,RGB(0,0,160),RGB(255,255,255))","Column")

this.setall("DynamicForecolor", ;
  "IIF(RECNO()=grno,RGB(255,255,255),RGB(0,0,0))","Column") 

When the grid on the form is started, the highlighted line is blue with white text which works perfectly and each changes accordingly as you scroll though.

Some of the records in a column with the table name JOBSTATUS have the letter "R" (without the quotes) to show it is a refund.

What I am trying to achieve is that when the form is loaded, any records with the condition JOBSTATUS="R" that only the cell within the grid will be shown as the back colour being red and the fore colour in white.

There are several FAQ's in the forum but none that give any information about changing a single cell linked to a condition.

Any guidance or suggestion would be appreciated.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

Well, there is no setting for a single cell, but for a single column, simply don't use setall but only set a certain column1,2,3,4.DynamicBackcolor and DynamicForecolor.

To only do that for rows with "R" in a certain table column, that is your IIF condition.

So after

CODE -->

this.setall("Dynamicbackcolor", ;
  "IIF(RECNO()=grno,RGB(0,0,160),RGB(255,255,255))","Column")

this.setall("DynamicForecolor", ;
  "IIF(RECNO()=grno,RGB(255,255,255),RGB(0,0,0))","Column") 
you add

CODE

This.column3.Dynamicbackcolor="IIF(field='R',RGB(160,0,0),RGB(255,255,255))"
This.column3.DynamicForecolor="IIF(field='R',RGB(255,255,255),RGB(0,0,0))" 

Notice within double quotes the use of double quotes is not possible, therefore 'R'.
Of course Column3 is merely a random choice, you know best which column you want changed.

Also notice, that column will differ in the coloring of every other column, there is no easy waay to combine all coloring expressions, so you get the overlap coloring conditions. Either you live with that or you introduce a more complex matrix of expressions and colors via ICase statements covering all cases of one or the other or both conditions being true with all the mixed colors needed to display that.

Bye, Olaf.

RE: Single cell colour change depending on condition

If your conditions get much more complex it might pay to use the oleboundcontrol and bind an Excel spreadsheet, in which you can have conditional formatting and coloring per Cell. Especially if you have knowledge about how to use that already. You then of course also can make use of all other Excel/spreadsheet features like formulas and automatic computations.

Bye, Olaf.

RE: Single cell colour change depending on condition

(OP)
Hi Olaf

Just tried your suggestion and it's given us the desired effect.

I appreciate your prompt and valued post.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

Steve,

I see that Olaf has given you a good answer. I've nothing to add to that. However, if you need any further information about conditional formatting in a grid, I recommend that you peruse Conditional formatting in a Visual FoxPro grid, which explains the whole thing in some considerable detail.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Single cell colour change depending on condition

(OP)
That link looks very interesting Mike

Also appreciate your post.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

(OP)
Following on from Mike's post, I viewed the web page link and found a line of code which I added in the Init procedure of the grid:

CODE -->

This.column13.DynamicForeColor="IIF(JOBSTATUS<>'R', 0, 255)" 

This shows the "R" in red with a white background instead of the background being red. Having tried both options, the latter appears to be more pleasing to the eye however one slight problem I have is that at the moment, the highlighted line for each record still, as expected, shows a blue background with a white fore colour for the exception of column13 which, when highlighted has a black fore colour, blue back background and the fore colour remains black (where it should be white when highlighted).

I hope the above makes sense.

This isn't a problem for the "R" condition records but it would be better (cosmetically) if the records <>"R" could remain blue back colour, white fore colour when highlighted.

Any suggestion would be appreciated and if further clarification is required, please let me know.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

It's the same as "IIF(field='R',RGB(255,255,255),RGB(0,0,0))", just <>'R' is the inverse condition and so black comes before red.

You have the problem I already described, you can't combine all possible combinations of conditionsthis way. For you JOBSTATUS column the other coloring doesn't apply, unless you would have a more complex ICASE with 4 conditions 1. JOBSTATUS = 'R' AND RECNO()=grno => color1, 2. JOBSTATUS = 'R' only => color2, 3. RECNO()=grno only => color3 and 4. otherwise => color4

Bye, Olaf.

RE: Single cell colour change depending on condition

(OP)
Appreciate the post Olaf. It's not something that will hold us back so I'll look into what you have suggested and post back when I have a sollution.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

If you can't wrap your head around IIF por ICASE you can also write a verbose form method returning a color and simply call it from the DynamicBackColor property, setting it to the call as in Grid.ColumnX.DynamicForeColor = "Thisform.GridForecolor(param1,param2)".

Obviously more ideal to add this method to the grid, but that's not possible after you adde3d the native grid to a form, that will require first creating a grid class to then use in forms.

Bye, Olaf.

RE: Single cell colour change depending on condition

(OP)
Hey Olaf

Your last post is a bit complex for my knowledge but I have found something to do with ICASE.

I will post back when I have a sollution.

Thank you

Steve Williams

RE: Single cell colour change depending on condition

My last post is just suggesting you can write a normal IF or case, long multiline code, in a form or grid method. This will be easier than the necessety to cramp all the necessary logic into a single code line. ICASE is what I suggested earlier, as it's capable to do more cleanly, what IIF only can do as ugly unreadable nested IIFs.

If you don't know yet how to add a form mehthod, you simply edit your form and choose the menu item Form->NewMethod. The rest follows.

Bye, Olaf.

RE: Single cell colour change depending on condition

(OP)
Appreciate the update Olaf

Thank you

Steve Williams

RE: Single cell colour change depending on condition

(OP)
Just to update this thread for closure, we used Olaf's suggestion as below as the "R" relates to refunds and as there are not many of those (thankfully), the view in the grid is more than acceptable.

CODE -->

This.column3.Dynamicbackcolor="IIF(field='R',RGB(160,0,0),RGB(255,255,255))"
This.column3.DynamicForecolor="IIF(field='R',RGB(255,255,255),RGB(0,0,0))" 

I appreciate the other posts on this thread.

Thank you

Steve Williams

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close