Conditional Formatting based on Other Cell Value Excel2007

Topics: Developer Forum, User Forum
Dec 6, 2011 at 6:08 PM

Hi,

 

I'm trying to setup conditional formatting using the excel2007 writer so that for example Cell B1 will be Yellow is C6=1, B2 will be Yellow is C6=2, etc., etc.

 

The only examples I've found so far for conditional formatting are based on the cell itself, ie. Set B1 to a colour based on the value of B1.

 

Is this possible, or has someoone for an example

 

Rgds

Chris

Dec 7, 2011 at 11:39 AM

I have this working using the EQUAL operator, but now the final conditional formatting I am trying to acheive it is repilcate this

For Cell B218 if the formula below is true then it highlights the cell yellow.

=AND(H218>0,H218<=10)

 

How can this be acheived in PHPExcel?

Coordinator
Dec 7, 2011 at 2:33 PM
Edited Dec 7, 2011 at 2:41 PM
 $objConditional = new PHPExcel_Style_Conditional();
 $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
 $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
 $objConditional->addCondition('AND(H218>0,H218<=10)');
 $objConditional->addCondition(TRUE);
 $objConditional->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);
 $objConditional->getStyle()->getFont()->setBold(true);
 $objPHPExcel->getActiveSheet()->getStyle('B218')->setConditionalStyles(array($objConditional));

 

Note that you'll have to set rules individually for a range of cells, as

$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('B2'), 'B3:B7' );

will not work with conditional styles using expressions or referencing other cells

 

Using "between" would probably be better in your case (though I've not tested it with cell references), but the formula-based example shown above is useful as a reference for others that might want to use more complex formulae in conditional formatting.

 $objConditional = new PHPExcel_Style_Conditional();
 $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
 $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_BETWEEN);
 $objConditional->addCondition('H218');
 $objConditional->addCondition(0);
 $objConditional->addCondition(10); 
 $objConditional->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);
 $objConditional->getStyle()->getFont()->setBold(true);
 $objPHPExcel->getActiveSheet()->getStyle('B218')->setConditionalStyles(array($objConditional));
 
Dec 7, 2011 at 3:52 PM
Thanks for this.

Its working a charm.

Rgds
Chris

On 07/12/2011 02:33 PM, MarkBaker wrote:

From: MarkBaker

 $objConditional = new PHPExcel_Style_Conditional();
 $objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
 $objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_EQUAL);
 $objConditional->addCondition('AND(H218>0,H218<=10)');
 $objConditional->addCondition(TRUE);
 $objConditional->getStyle()->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_YELLOW);
 $objConditional->getStyle()->getFont()->setBold(true);
 $objPHPExcel->getActiveSheet()->getStyle('B218')->setConditionalStyles(array($objConditional));

Note that you'll have to set rules individually for a range of cells, as

$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('B2'), 'B3:B7' );

will not work with conditional styles using expressions