I am looking to do conditional formatting based on a single cell within a column. The formatting will be based on the value; ie under 80=RED, between 80-90=Yellow, 90-100=green.
This column has the total across multiple columns and is a I9C.

Right now I am doing the following but it isn't applying the formatting.

Any ideas why? N8 is the column that needs to be formatted

DEFMACRO=GREEN,
MACTYPE=RULE
WHEN N8 GT 90,
$
DEFMACRO=YELLOW,
MACTYPE=RULE
WHEN N8 GT 80,
$
DEFMACRO=RED,
MACTYPE=RULE
WHEN N8 LT 80,
$
DEFMACRO=WHITE,
MACTYPE=RULE
WHEN N8 GT 100,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR=RED,
MACRO=RED,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='LIME',
COLOR='BLACK'
MACRO=GREEN,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='YELLOW',
COLOR='BLACK',
MACRO=YELLOW,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='(RGB(#F5F5F5) RGB(#EEEEEE))',
COLOR='BLACK'
MACRO=WHITE,
$

This message has been edited. Last edited by: wlwright1,

Are you sure the column is N8. Try just making that column Red to see if it's really 8. It can be tricky sometime.

Yup I tested it and it works but highlights both cells in that column when I only want the cell on the second row to be conditionally formatted

Warren,

quote:

This column has the total across multiple columns and is a I9C
...
it works but highlights both cells in that column when I only want the cell on the second row to be conditionally formatted

Can you explain both quotes?
Is the total a field or a computation using "ACROSS-TOTAL" or "ROW-TOTAL"?
What do you mean by "the second row"?

Could you repro using the CAR file and post the code?

Like Daniel says.. could you post a CAR sample...


While we wait for that...
...have you considered using COMPUTE field to determine the styling?

See sample:

TABLE FILE CAR
SUM 
     SALES
     COMPUTE COLORME/A10 = IF FST.CAR EQ 'TOYOTA' OR FST.COUNTRY EQ 'ITALY' THEN 'RED' ELSE ''; NOPRINT
BY  LOWEST COUNTRY
BY  LOWEST CAR
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
     DEFMACRO=COND0001,
     MACTYPE=RULE,
     WHEN=N4 EQ 'RED',
$
TYPE=DATA,
     COLUMN=N3,
     BACKCOLOR='RED',
     MACRO=COND0001,
$
ENDSTYLE
END

The way that you structured your macros you will not get intended results for value of 80, 90 or 100. Instead of just using GT (greater than) and LT (less than) try GE (greater than or equal to) and LE (less than or equal to).

Here is the code that I have. I have a counter that specifies how the total for each row is calculated. This is done via a compute function.

TABLE FILE POPHOLD3
PRINT
POPHOLD3.POPHOLD3.POP_DESCRIPTION AS 'Description'
POPHOLD3.POPHOLD3.ACTIVE_VALUE AS 'Active'
POPHOLD3.POPHOLD3.DEFERRED_BENEFIT_DUE_VALUE AS 'Deferred,Benefit Due'
POPHOLD3.POPHOLD3.TERM_DEAD_NO_CALC_VALUE AS 'Term/Death,No Calc'
POPHOLD3.POPHOLD3.OTHER_VALUE AS 'Other'
POPHOLD3.POPHOLD3.NO_BENEFIT_DUE_VALUE AS 'No,Benefit Due'
COMPUTE C_TOTAL/D12C = IF POPHOLD3.POPHOLD3.COUNTER EQ 1 THEN POPHOLD3.POPHOLD3.ACTIVE_VALUE + POPHOLD3.POPHOLD3.DEFERRED_BENEFIT_DUE_VALUE + POPHOLD3.POPHOLD3.TERM_DEAD_NO_CALC_VALUE + POPHOLD3.POPHOLD3.NO_BENEFIT_DUE_VALUE + POPHOLD3.POPHOLD3.OTHER_VALUE ELSE IF POPHOLD3.POPHOLD3.COUNTER EQ 2 THEN ( MIN.POPHOLD3.POPHOLD3.TOT_CLEAN_CNT / MIN.POPHOLD3.POPHOLD3.TOT_SSN_CNT ) * 100; AS 'Total'
BY POPHOLD3.POPHOLD3.COUNTER NOPRINT

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET STYLE *
INCLUDE = db_ahtml_style,

TYPE=HEADING,
LINE=1,
JUSTIFY=LEFT,
$

TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
$
TYPE=HEADING,
LINE=3,
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=2,
STYLE=NORMAL,
$
TYPE=HEADING,
LINE=5,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
$
DEFMACRO=GREEN,
MACTYPE=RULE
WHEN N8 GT 90,
$
DEFMACRO=YELLOW,
MACTYPE=RULE
WHEN N8 GT 80,
$
DEFMACRO=RED,
MACTYPE=RULE
WHEN N8 LT 80,
$
DEFMACRO=WHITE,
MACTYPE=RULE
WHEN N8 GT 100,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR=RED,
MACRO=RED,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='LIME',
COLOR='BLACK'
MACRO=GREEN,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='YELLOW',
COLOR='BLACK',
MACRO=YELLOW,
$
TYPE=DATA,
COLUMN=N8,
BACKCOLOR='(RGB(#F5F5F5) RGB(#EEEEEE))',
COLOR='BLACK'
MACRO=WHITE,
$

Is this what you mean?:

  
TABLE FILE CAR
PRINT 
     COUNTRY
     DEALER_COST
     RETAIL_COST
     SALES
     COMPUTE COUNTER/I1 = IF LAST COUNTER EQ 0 THEN 1 ELSE 0; NOPRINT 
     COMPUTE TTL/D7 = IF COUNTER EQ 0 THEN RCOST + DCOST ELSE SALES * 1.5;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
     DEFMACRO=COUNTER1,
     MACTYPE=RULE,
     WHEN=N5 EQ 1,
$
TYPE=REPORT,
     GRAPHCOLOR='GREEN',
$
TYPE=DATA,
     COLUMN=N6,
     BACKCOLOR='RED',
     MACRO=COUNTER1,
$
ENDSTYLE
END

Daniel,

Well counter is specified in another hold file with a compute of:
COUNTER/I5C=LAST COUNTER+1;

So i am then saying when counter eq 1 then add the values to get the total and when eq 2 then it takes two additional columns and divides them and multiplies for 100 for percent.

Are you saying that I should have another compute field to specify when rule it follows?

COMPUTE DISPLAY/I1 = IF C_TOTAL GE 100 THEN BLANK ELSE IF C_TOTAL GE 90 THEN GREEN ELSE IF C_TOTAL GE 80 THEN YELLOW ELSE IF C_TOTAL LT 80 THEN RED ELSE 0 NOPRINT

Maybe, just maybe, it doesn't like the fact that you are using colours as your macro names?

So I have added a column called 'Display' with the following compute statement:
COMPUTE DISPLAY/A10= IF C_TOTAL GE 100 THEN 'FORMATNO' ELSE IF C_TOTAL GE 90 THEN 'GOOD' ELSE IF C_TOTAL GE 80 THEN 'WARNING' ELSE IF C_TOTAL LT 80 THEN 'BAD'; AS 'Display'

This works and puts the correct text in that column for the criteria.

I then updated my macros to be the following
DEFMACRO=GOOD,
MACTYPE=RULE
WHEN N9 EQ GOOD,
$
DEFMACRO=WARNING,
MACTYPE=RULE
WHEN N9 EQ WARNING,
$
DEFMACRO=BAD,
MACTYPE=RULE
WHEN N9 EQ BAD,
$
DEFMACRO=FORMATNO,
MACTYPE=RULE
WHEN N9 EQ FORMATNO,
$

I kept the color wanting to be populated in the N8 column since that was the column with the calculated values.

The cell still isn't being colored at all with these new changes.

What am i doing wrong?

quote:

DEFMACRO=GOOD,
MACTYPE=RULE
WHEN N9 EQ GOOD,
$

Your DEFMACRO syntax is incorrect.

(Compare yours to the punctuation in Dan's example)

Warren,


I suppose you ran my example and saw that it gives you the result you wanted. I used the COMPUTE statement just to mimic your COUNTER. Your COUNTER should be fine.
If you have problems with the N notation, use the fieldnames instead. Also, I see that you do not have a comma after MACTYPE=RULE.

Daniel,

Thank you very much for all of your help. I was able to get it to work. I had to do the following since I made the Display column having the text for the rule.

DEFMACRO=GOOD,
MACTYPE=RULE,
WHEN=N9 EQ 'GOOD',
$
DEFMACRO=WARNING,
MACTYPE=RULE,
WHEN=N9 EQ 'WARNING',
$
DEFMACRO=BAD,
MACTYPE=RULE,
WHEN=N9 EQ 'BAD',
$
DEFMACRO=FORMATNO,
MACTYPE=RULE,
WHEN=N9 EQ 'FORMATNO',
$

After this it works great. Thank you everyone for all of your help.

How do I mark something as closed?

quote:

How do I mark something as closed?

Edit the original post and refix the post title with [CLOSED]. See the complete instructions HERE, as shown above as tag your topic with keywords.