Conditional Formatting

Reena1024

New Member
Joined
Dec 3, 2004
Messages
32
Hey!

In one row, I would like to do the following.

If A3 has any writing in it, but B3 does not, turn the entire row blue...but if A3 and B3 both have writing in them, then the row should remain clear or just no background color.

And if A3 is equal to the word "Pending" then turn the entire row pink.

And if nothing is written in A3 or B3 then the row is just clear.

Does anyone know how to do this?

I tried conditional formatting and for the first part I got it, but to do the pending part, another statement just won't work.

Any help?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

smi123

Board Regular
Joined
Nov 7, 2005
Messages
67
From your email it would appear that "Pending" takes priority over your other criteria..thus.. placed first will work

Condition 1
Formula is =$A3="Pending"
Pattern = Pink

Condition 2
Formula is =AND($B3="",$A3<>"")
Pattern = Blue
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
So, if A3 says "pending" and B3 is blank should the row be blue or pink?

assuming it's pink then condition one should be

=$A3="pending"

pink format

condition two

=(len($A3)>0)*(len($B3)=0)

blue format
 

Reena1024

New Member
Joined
Dec 3, 2004
Messages
32
Additional Problem with Conditional Formatting

I have an additional problem to this. I found out how to do it with conditional formatting..apparently order matters a lot.

But in A3 I sometimes have a date, and otherwise I have the word "Pending"

I am running counts on the next page to see how many times we have something in the month of November and December, but if I add in the word Pending onto that page, my counts get all screwy.

Anyone know how to fix this?

My formulas currently are as such:

This first one calculates how many times something is written in the sheet has the date in November, has something written in B3, and is tied to Bob. But what happens when I try to calculate how many pendings there are for Bob by counting the word "Pending" all of these formulas go nuts.

=SUMPRODUCT(--(MONTH(Track!A3:A1000)=11),--(Track!D3:D1000="Bob"),--(Track!B3:B1000<>""))

Does this make sense?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,622
Messages
5,832,739
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top