Conditional Formatting

**AccountantCost** · 09-10-2009, 08:43 PM

I have column A containing numerical data. Some of the cells in this column are highlighted / filled in a yellow color. Other cells in this column have no color format.

Is there a formula that can be created to look at the data in column A and if a cell is highlighted yellow, an asterisk (*) is placed in column B in the same row?

If the cell in column A is not highlighted yellow, then the formula does nothing.

**Palmetto** · 09-10-2009, 08:53 PM

There is no built-in formula to determine the fill color of a cell. You would have to use VBA.

However, what is the criteria that caused some cells to be filled with yellow, even if it was done manually?

A formula could be developed if the criteria are known. Tell us more or upload a sample workbook.

**AccountantCost** · 09-11-2009, 12:00 AM

Column A contains amounts - many of which net to zero (i.e. there is a debit for $10 in one cell and a credit for $10 in the cell below).

For those amounts that net to zero, I have manually highlighted yellow.

I would like a way to identify cells that are not highlighted yellow without manual effort (I was thinking of placing an * in the adjacent column so that I could resort the data and group all the cells that net to zero).

**JBeaucaire** · 09-11-2009, 12:17 AM

Actually there are some new color-detecting capabilities in Excel 2007. Since you have not filled out your forum profile nor indicated IN your post what version you're using, Excel 2007 users will most likely not offer assistance from these new features.

**Cheeky Charlie** · 09-11-2009, 05:51 AM

so a formula like =if(a1-a2=0,"*","") would work?

**JBeaucaire** · 09-11-2009, 09:32 AM

Originally Posted by Cheeky Charlie

so a formula like =if(a1-a2=0,"*","") would work?

That would only work if the +/- entries appeared directly next to each other, which I am sure it not the case.

1) Put this formula in B2 and copy down. It will put a * in every cell that has an opposing match nearby. You can only copy this formula downward.

=IF(A2=0, "", IF(A2<0, IF(COUNTIF($A$1:A2,A2)=COUNTIF($A$1:$A2,-$A2), "*", ""), IF(ISNUMBER(MATCH(-A2,A2:$A$10000,0)), IF(INDEX(B2:$B$10000,MATCH(-A2,A2:$A$10000,0))="*", "*", ""), "")))

2) The manual highlighting of matching rows is not necessary. With this formula in place, you could now use a conditional formatting rule to turn the cells yellow automatically if a * is in the adjacent B column.