CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
user3185735
user3185735

Reputation: 223

Excel VBA - Conditional formatting of cells which contain formulas

Cell E4 contains a formula which counts the number of elements stored in a previous sheet and then substracts them from the total counted elements on this sheet. For example

I would like to use conditional formatting so that if the value in cell E4 is smaller than D4 the color of the text should be red and green if the value is above. Sadly, this doesn't work with conditional formatting and I presume it has something to do with the fact that cell E4 contains a formula. Actually as can be seen in the print screen, I would like to apply this criteria for multiple cells. So a comparison of the values in the columns E and D

Print screen:

enter image description here

Upvotes: 0

Views: 295

Answers (2)

Rowen Chumacera
Rowen Chumacera

Reputation: 529

You can try this formula for the conditional formatting of E4:

=VALUE(MID($E4, 2, LEN($E4)-2))<$D4

Copy E4 and paste the formatting to succeeding cells.

What it does is remove the parentheses (which convert the cell value to a string), and convert the value to a number which can be compared to values in column D.

Upvotes: 0

Fratyx
Fratyx

Reputation: 5797

I think your problem is that you try to compare a numeric value with a string because the formula in E4 results in a string. I would suggest you replace this formula by =D4-'09.16'!D4 i.e. just the calculation without the brackets. Then in the regular formatting you apply as user defined format string (0) to that cell. That means the value will be surrounded by brackets. So you will get the same visual result while the internal representation of this cell will still be numeric. The comparison of the values in your conditional formatting should work in this way.

Upvotes: 1

Related Questions