Excel VBA - Conditional formatting of cells which contain formulas

Question

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:

so that if the value in cell E4 is smaller than D4 Shouldn't that be F4 instead of E4? — Siddharth Rout
– Siddharth Rout, Commented Sep 22, 2016 at 7:15
Wouldn't that condition come down to '09.16'!D4 < 0 if you are comparing D4 to `D4-'09.16'!D4``? — arcadeprecinct
– arcadeprecinct, Commented Sep 22, 2016 at 7:27

Fratyx · Accepted Answer · 2016-09-22 07:17:01Z

1

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.

answered Sep 22, 2016 at 7:17

Fratyx

5,8271 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rowen Chumacera · Accepted Answer · 2016-09-22 08:06:04Z

0

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.

answered Sep 22, 2016 at 8:06

Rowen Chumacera

5293 silver badges10 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Conditional formatting of cells which contain formulas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related