Colorize cells with same values

QuestionEdit

I have a spreadsheet where I need to change cell's background color that have the same value.

- The spreadsheet has four columns: C, D, E and F. - Beginning at row 5 and going all way down, when two/three/all the four cells have the same values, set the background color of both cells to a green. - In case of having two cells matching one value, and other two matching another value, color each group with a different color.

Is this possible? In an automatic way while I'm working on the spreadsheet?

(and sorry for my poor English!)

Rodrigo

AnswerEdit

Rodrigo,

the requirement to mark duplicate values in the same row can be done quite easily with conditional formatting.

You would select columns C:F with C1 as the active cell, then in Excel 2007 you would select conditional formatting from the home tab. then select new rule then select use a formula to select which cells to format

put in the formula =COUNTIF(C1:F1,C1) (again, C1 must be the active cell when you do this)

then select the formatting (color) you want to apply.

that should do the first part.

If you want to do the second part, that would be much more comlex. You would need to put in two rules. The first rule would be =IF(C1<>"",IF(SUMPRODUCT(COUNTIF($C1:$F1,$C1:$F1))<>8,COUNTIF($C1:$F1,C1)>1,C1=MIN($C1:$F1))) the second would be =IF(C1<>"",IF(SUMPRODUCT(COUNTIF($C1:$F1,$C1:$F1))<>8,COUNTIF($C1:$F1,C1)>1,C1=MAX($C1:$F1))) Each rule would have a different color assigned. that worked for me.

Note that I have given instructions for xl2007. The instructions would be similar for2003 and earlier, but where the commands are located would be different.

If you need a sample workbook, contact me at