Conditional formatting - using a value to control color of bar
 

Dear Excel gurus,

I am trying to make use of the data bars option under Conditional Formatting. I want the value of the conditonally formatted cell to determine the length of the bar. Thus far everything is fine! Then I want another value to determine the color of the bar (red/yellow/green).

The solution that I expected to work was to add two more data bar rules and separate them by rules based on the other value. The latter rules would then be set to "Stop if true".

The result I get is that only the firts data bar rule seems to apply. This is the case even if I remove the "stop-rules".

I have attached a zipped xlsx file with an example. I have also attached a picture of my conditional formatting dialog.

Any ideas on how to accomplish this?

I am using MS Office Standard 2010. 32-bit.

Conditional formatting - using a value to control color of bar
 

Hi,

Am Mon, 20 Oct 2014 08:39:32 +0100 schrieb Horus:

I am trying to make use of the data bars option under Conditional
Formatting. I want the value of the conditonally formatted cell to
determine the length of the bar. Thus far everything is fine! Then I
want another value to determine the color of the bar
(red/yellow/green).

you have to use VBA.

Please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for workbook "CF_issue_Horus"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Conditional formatting - using a value to control color of bar
 

Hi,

Am Mon, 20 Oct 2014 10:11:28 +0200 schrieb Claus Busch:

for workbook "CF_issue_Horus"

download the file because macros are disabled in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you Claus!

I was hoping to avoid a macro for this, but you sound certain that this is not an option. Is this a known limitation of the feature? I have not found any information as to why this should not work.

Thank you for providing a working example. It may actually be less work than implementing the Conditional Formatting as I intended, since I will need to do it for several non-adjacent cells.

Best Regards,
Horus (Fredrik Lönn)

Conditional formatting - using a value to control color of bar
 

Hi Frederik,

Am Mon, 20 Oct 2014 11:06:32 +0100 schrieb Horus:

Is this a known limitation of the feature? I have not
found any information as to why this should not work.

for the data bar you can only select a color but you could not refer it
to a value


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I understand that, which is why I tried with the stop-if-true rules between three different data bar formattings. I don't see why that should not work, but for now I will resort to VBA as you proposed.

Thank you,
Fredrik