Excel VBA - Conditional formatting of cells which contain formulas

0
votes

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

  • previous sheet contains 3 elements
  • current sheets contains 0 elements (see below cell D4)
  • result is (-3) (see below cell E4 ; formula is ="(" & D4-'09.16'!D4 & ")"

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

2
excelexcel-formulaconditional-formatting
so that if the value in cell E4 is smaller than D4 Shouldn't that be F4 instead of E4? – Siddharth Rout
Wouldn't that condition come down to '09.16'!D4 < 0 if you are comparing D4 to `D4-'09.16'!D4``? – arcadeprecinct

2 Answers

1
votes

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.

0
votes

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.