PDA

View Full Version : 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
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'.

icthus123
10-12-2007, 06:18 AM
It may well have, as I can amend the validation colour without getting a change in font in XL 2003 on XP.
p45cal

works fine for me too in office 2000!

lad
10-12-2007, 06:30 AM
Yep, just sent it to a colleague who has 2003 and we sorted it that way!

Hmmm.... 2007 bug maybe?

Cyberdude
10-14-2007, 12:57 PM
Just tried it on my Win XP 2003 and it worked OK.
Interesting.

Carl A
10-14-2007, 04:11 PM
Must be a bug my Excel 2007 Displays the same as lads. Works fine in Excel 2000