• 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 formating based on dynamic cell

T

tcolton

New Member
First off...thanks so much for the amazing site. I've got everyone in my company checking out your blog!


Now on to my problem. I'm trying to get a set of cells to change format based on the value in another cell. Pretty simple. Using conditional formatting I can change the color, font, etc., but I can't get it to change the number format from number to percentage. Take a look at the link to a dummy example.


www.apqceducation.org/excel/test.xlsx


Using the scroll bar to move through the records, I want the cell format to change in D8:E15 to percentage when S7 gets to 48 or above and then back again to number format when it goes below 48.


The funny thing is it will work when I apply the conditional formatting when S7 is above 47 and then when I go below 48 it will change to number format, but it will not change it back to a percentage when you go above 47 again. Hope that makes sense.


I must be missing something!!!!
 
I see that you are using excel 2007. So this must be possible. First select all the cells on which conditional formatting is to be applied and then set their cell format to Number. Now apply conditional formatting by adding a new rule and in the format box, select percentage from "Number" tab. If your formatting rules are correct, this should work naturally.
 
I can get it to work in one direction, but not change the format back to a different format. For example, I can set the rule to change the cell format to percentage if S7>40. If I add another rule that says S7<=40 to change the format to number it will not work. So essentially it will change the cell format one time, but not twice. It is weird because it will change other characteristics of the cell, but just not the number format. I was trying not to use VBA, but I guess I'll have to. Thanks for the response though, and keep up the great work.
 
I have tested it with simple conditions (< and >) and it is working for me. I have set only one condition. The other is obvious, so I used cell formatting. Can you try to remove the <= condition and see if it works. Essentially you can format the whole range as if it is <=40 and then apply conditional formatting for >40.
 
I don't know how to apply conditional formating to a cell, based on the value of another cell or on the basis of a formula which depends on the value of another cell. Probably easy, but I need some help. Thanks.
 
Swensor

Have a read of

http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/


in regards to basing a conditional format on another cell

if you are trying to set the conditional format for cell B2 based on B1

put the cursor in B2

Seect condional formatting

Use a formula and try something like =B1>10

set your formatting

change the values of B1 and see what happens
 
You must log in or register to reply here.
Back
Top