Conditional VBA?

[KennyA]

Hello all!
I am not sure how to label what I am attempting to accomplish but here it goes.
Excel 2013, Windows 7
All on Sheets ("Sheet1")

Cell ID's
In cell B8 I have a manual entry for the number of days to actually perform the task.
In cell B9 I have a manual entry for number of days before B8 Value as the warning trigger.
In cell B11 I have a simple formula to calculate the remaining days before the task is due.
In Cell F33 I have the percent of components on hand.

What I am trying to accomplish:
I want cell B12 to turn RED and have text that says "DANGER" in white letters in cell B12 if:
B8+B9 is > B11 And F33 is <100%
This will signal the warning that the drop dead date is nearly not achievable.

so let's say this for further explanation:
B8 = 5 days and B9 = 5 days. That is 10 days
B11 = 13 days and F33 is < 100%
No warning.
But if B11 hits 9 days and F33 is still < 100% I get warned.

Any Ideas? Thanks in advance

 

[63falcondude]

Does it have to be done using VBA? It is rather straightforward using a formula and Conditional Formatting.

If you want to go this route, start by entering the formula =IF(AND(SUM(B8:B9) > B11,F33 < 1),"DANGER","") in B12.

Now select B12, Conditional Formatting, New Rule, Use a formula, =AND(SUM(B8:B9) > B11,F33 < 1), Red Fill, White Font, OK.

 

[KennyA]

I did what you said to do.
I get an error "There is a problem with this formula" when entering the formula in the conditional formatting step
Now select B12, Conditional Formatting, New Rule, Use a formula, =AND(SUM(B8:B9) > B11,F33 < 1), Red Fill, White Font,

 

[63falcondude]

I did what you said to do.
I get an error "There is a problem with this formula" when entering the formula in the conditional formatting step
Now select B12, Conditional Formatting, New Rule, Use a formula, =AND(SUM(B8:B9) > B11,F33 < 1), Red Fill, White Font,

That's odd... I just cleared everything and followed the steps exactly and it seems to work fine on my end.

You can try changing the CF formula to =B12="DANGER" and that should work too. That is as long as you have the IF formula in B12 already.

 

[KennyA]

That did not work either. I did also try copying and pasting the CF formula just as you wrote it and it does not work.

What about trying it in VBA?

 

[63falcondude]

That did not work either. I did also try copying and pasting the CF formula just as you wrote it and it does not work.

What about trying it in VBA?

Start by creating a sample worksheet from the following data (I moved F33 up to F9 for display purposes). See if you can get it working on that and then you can see why it is not working on your actual data.

Excel 2010
	A	B	C	D	E	F
8	# Days to preform task	5
9	# Days before B8	5			% of components on hand	99%
10
11	Remaining Days	9
12		DANGER
Sheet2

Cell Formulas
Range	Formula
B12	=IF(AND(SUM(B8:B9) > B11,F9 < 1),"DANGER","")

 

[KennyA]

This is what I see.

 

[KennyA]

Apparently the image did not go through.
I get the error in the CF formula step.

 

[63falcondude]

Apparently the image did not go through.
I get the error in the CF formula step.

Did you copy and paste my example from post #6 and then try it on that? If so, are you able to get the word "DANGER" into cell B12?

 

[KennyA]

Yes and DANGER did show in cell B12