• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting with Gradient Fill

Abernein

New Member
Hello,

I am using an Excel table to track action items decided in meetings. For instance, I will have an action like "Paul needs to talk to Roger" in A1, then in B1, I have the date this is supposed to happen, say "3/1/2017", and in C1 I have used conditional formatting/gradient fill to track the progress of the task, say "50%". Till here, no problem. Now, the gradient is only one color. What I would like to do is that if the progress is 50% for instance or anything different than 100%, and the date where the task is supposed to be completed has not passed, I would like the gradient to appear in green (this will tell me that this task is not late). If the date has passed, I would like the gradient to be in red.

Thanks in advance for your help with that
Pascal
 
Pascal

Firstly, Welcome to the Chandoo.org Forums

Select Cell C1
type: =100%-B1

Now goto the Home, Conditional Formatting tab
Click on Data Bars and Select the Red Gradient Fill
Apply

Now Select c1 again
goto the Home, Conditional Formatting tab
Manage Rules
Select the Data Bar and clcik on Edit Rule
Select the Negative Values and Axis ... dialog
Set the Fill and Border Colors to Green
Ok, Ok and Apply

Now Select C1
Ctrl+1
Select the Number Tab, Custom
In the Type box, type: ;;;

Enjoy

upload_2017-3-7_22-31-6.png

upload_2017-3-7_22-31-27.png
 
Hi,

I'm not sure you can have different Data bars in the same cell based on conditions... you can have them change color if the value is negative but I don't know about the date condition.
However, with a little VBA this is easily accomplished (refer to attached)
Try changing the dates and values in "B1:C5" and the colors and size of the bar will change accordingly.

I hope this helps
 

Attachments

  • Chandoo.xlsm
    15.7 KB · Views: 9
I have attached a non-VBA solution. Unfortunately, the option to make both positive and negative data bars go the same direction does not seem to work for me, so I have one solution that uses cell fill with a gradient effect and the data bar option in a separate column.

I hope that helps.

Regards,
Ken
 

Attachments

  • TestCF_KenU.xlsx
    9.4 KB · Views: 5
Hey,
Thank you for your help; it does work exactly as I wanted.

Pascal

Hi,

I'm not sure you can have different Data bars in the same cell based on conditions... you can have them change color if the value is negative but I don't know about the date condition.
However, with a little VBA this is easily accomplished (refer to attached)
Try changing the dates and values in "B1:C5" and the colors and size of the bar will change accordingly.

I hope this helps
 
Back
Top