Formula in Conditional Formatting

[JulieG]

I need to conditional format row E (Color Status), Based off of Row B (Credit Limit), C (Credit Used) or D (Available Balance).
Basicly its just a Customer Credit Limit Sheet. When a customer is within 15% of there credit limit a yellow box appears in colomn D, in there row. If there at or above there credit limit a red box appears. All good standing customers will have green boxes.

Its just a quick visual referance that we can update daily.
I would like to use percentages in my formula if possible.

Thanks

 

[SteveO59L]

1st condition

"Formula Is" =c1<b1*0.85 colour Green

2nd condition

"Formula Is" =c1<=b1 colour Yellow


3rd Condition


"Formula Is" =c1>b1 colour Red

 

[texasalynn]

Welcome to MrExcel Board....

is this what you need?

Excel Workbook
	A	B	C	D	E
1	ID	Credit Limit	Credit Used	Available Balance	Color Status
2	Cust1	5000	4250	750	15%
3	Cust2	15000	4000	11000	73%
4	Cust3	2000	2500	-500	-25%
Sheet4

 

[texasalynn]

forgot to give you the conditional format - select column D and enter like this:
<a href="http://s569.photobucket.com/albums/ss140/texasalynn/?action=view&current=conditionalformat.jpg" target="_blank"><img src="http://i569.photobucket.com/albums/ss140/texasalynn/conditionalformat.jpg" border="0" alt="Photobucket"></a>

 

[JulieG]

Yep.. Thats what i need. However, its not working for me. I have test figures in the cell. No cells change color when i apply the formatting.

I dont have the same screen as you. I use 2007 Student. To get to conditonal formatting i click on conditional formatting and select mangage rules > enter a new rule. from there i dont have the option to select "formulas Is". I can just type in my formula and assign a color.

 

[VoG]

When you click on New Rule you should get a dialog with 'Select a rule type' at the top. Click on 'Use a formula...' (the bottom choice) then enter the formula in the next box down.

 

[JulieG]

Yea.. I got that far. Its just that nothing is changing when i enter my formulas.
So my sheet Is like this:
A B C D
Company Credit limit Credit Used Available Balance
1 ABC 10000 9000 1000


So my formulas look like: =AND(B1<=15%,B1>0)
Conditional formatting should turn the D colomn yellow. It dosent!

 

[texasalynn]

are you highlighting Column D first and then selecting Conditional formatting?

And the formula you entered doesn't work with your scenario because there are not %'s in column B. You would have to work out the formula. Perhaps like
=AND(D2/B2<=0.15,D2/B2>0)

 

[JulieG]

Okay, So i have the formulas worked out for the most part. The last formula is something like this:

=C1>=B1

It does not work. I must be writing it wrong. What im trying to say is:

If C1 is greater then or equal to B1.. then turn red.

Any Ideas?

 

[texasalynn]

maybe this

=AND(C1>=B1,C1<>"")