Quirk with Cond Formatting?

**lad** · 10-12-2007, 01:20 AM

Hi,

I'm having trouble with what looks to be a quirk with conditional formatting.

I've created a formula in Column D of my worksheet that basically checks the values of numbers in Column C. If the number is above a certain value but below another value it changes the text of the cell in Column D to amber, if below a certain value it changes it to red.

In Column D I have used a webding font character (108) to insert a solid circle in every cell.

This works great except the amber and red look a little similar so I wanted to change the amber to a another colour. However, when I edit the conditional format rule to another font colour (that's all I change), the colour changes fine, but it also changes the font in all the relevant cells to a "|" instead of the solid circle!?!?

It's as if the font is removed. If I click on an affected cell a see in the little window bar where formulas usually pop up (sorry, forgot what it's called) a "|" symbol. Fair enough, however if I click on one of the cells what has the unaffected circle symbol in I also see a "|" in the formula window !!!

Confused!!

Any ideas anyone as to why this happens?

Cheers.

**mdmackillop** · 10-12-2007, 02:24 AM

Why not post your book so we can see this.

**lad** · 10-12-2007, 02:35 AM

Hi,

I can't do that due to the links to other workbooks and sheets within and the sensitive nature of the content. What I'll try and do is replicate the problem in a 'clean' workbook with just those two columns and post that if possible.

Cheers.

**lad** · 10-12-2007, 02:51 AM

OK. I think this should do it. I've copied the two columns across along with the cond formatting.

There are 3 rules. One to make the icon in cell Dx white, one for 'amber', one for red, depending on the value of the cell in Column C.

Now, I want to change the 'amber' colour as it's a bit close to the red, however whenever I amend the colour in the cond. format. rule it alters the view of the symbol in the cell.

By the way I'm using Excel 2007 whether that has any bearing.

Cheers.

**p45cal** · 10-12-2007, 04:06 AM

Originally Posted by lad

By the way I'm using Excel 2007 whether that has any bearing.

It may well have, as I can amend the validation colour without getting a change in font in XL 2003 on XP.
p45cal

**lad** · 10-12-2007, 04:32 AM

Thanks p45cal, weird one eh!

I've Excel 2003 on my laptop so I'll see if I can sort on that when I get my hands on it. Haven't got it with me at the mo'.