Conditional formatting on cells that contain a formula

[grin869]

Hi there,

i am an excel novice, but what i'm trying to achieve i would hope should be fairly straight forward, however i can't get it to work. I am trying to change the colour of a cell when it is greater than a certain value. This cell has a formula in it that calculates the percentage of 2 other cells. It works when i apply the CF for the first time, but if the cell value changes, the colour doesn't. Please help!!!

G

 

[p45cal]

1. Version of Excel?
2. What have you got now for the CF?
3. What 'certain value'?
4. Examples of typical values when:

(a) it works
(b) it doesn't work​

 

[grin869]

I've done some more testing, and if the formula in the target cell is a number, then all works fine. It's when it is a percentage it fails.

1. 2010
2. Currently have an Icon based rule where i get a red traffic light if the number is >= 3.2, and green if <3.2. This works fine with number but not %
3. The 'certain value' in this formula is 3.2, but need this to be 3.2%
4. hopefully the above explains this.

Many thanks for your help.

 

[p45cal]

3.2% is 0.032, so use >=0.032 and <.032

 

[grin869]

Sorted. Many thanks. I knew it would be something simple.

 

[twosixbw]

I have a similar problem whereby the conditional formatting doesn't update if the formula gives a different result.....

An example is if I have the conditional formatting set up so that if the formula returns "Heading" the formatting is bold. If it returns "Sub-heading" the conditional format is underlined but not bold however if the formula changes the result from "Heading" to "Sub-heading" the formatting stays the same until I either click on the formula or click on conditional formatting and apply it again.....

It should be:

Heading
Sub-heading

but if the formula is updated and the result changes I get:

Sub-heading
Heading

Hope that makes sense....

Any ideas how to make the conditional formatting update when the formula updates?

 

[p45cal]

The following updates immediately values in column E are changed:

Excel Workbook
	C	D	E
5	Heading		1
6	Sub heading		2
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5	1. / Cell Value equal to ="Heading"	Abc
C5	2. / Cell Value equal to ="Sub heading"	Abc
C6	1. / Cell Value equal to ="Heading"	Abc
C6	2. / Cell Value equal to ="Sub heading"	Abc

Can you upload a small file demonstrating your problem to the interweb somewhere (eg. box.net)?

ps. you haven't set Calculation to manual have you?

 

[twosixbw]

Thanks for your response....

Basically the problem seemed to be that I was using a VLOOKUP formula from a different sheet and the conditional formatting failed to trigger if the values updated....

I've found a way round the problem by including a macro as below which basically re-runs the first formula and thus updates all the conditional formatting!

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
Range("C8").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP((ROW()-6),'2C Calc'!R8C1:R372C2,2,FALSE)),"""",VLOOKUP((ROW()-6),'2C Calc'!R8C1:R372C2,2,FALSE))"
Range("B6").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Regards,

Phil