Conditional Formatting with different colours

[randomlychili]

Hello!

First off, I know my excel is in German, but I would prefer to ask in English, as it is easier for me.

This is an Excel sheet that will be used for sending out reminders for unpaid bills.

Here I can list what each column is for, although most of them should be quite irrelevant for the formula

A - you can enter the bill number
B - the date of the original bill
C - the name of the client
D - the amount on the bill
E - days the client has to pay the bill
F - automatically calculates the date the bill needs to be paid by (WHEN(B2="";"";B2+1.5*E2) - meaning they get a little longer to pay the bills than noted on the bill
G - date that the first payment reminder got sent
H - date that the second payment reminder got sent
I - date the bill got paid
J - comments

What I am trying to do, as you can probably tell from my conditional formatting attempt, is to have the row be orange when the first payment reminder gets sent, red if a second payment reminder gets send and green if the bill was paid. However, if I enter the formula as I have (please note that NICHT = NOT and ISTLEER = EMPTY) it obviously all automatically goes with orange since G is not empty in all of them... however G will always be filled in, H will sometimes be filled in, and in the end hopefully all of them will have something written in I...

I really hope this makes sense and someone can send me the right formula so that it's not all orange, but in fact in all three colours.

You can write the formula or tips or whatever in English, I can easily translate them to German from there (I work in German but do all research in English, so I am used to it)

Thank you in advance for all the help!

 

[etaf]

so something like

G - date that the first payment reminder got sent
H - date that the second payment reminder got sent
I - date the bill got paid
is to have the row be orange when the first payment reminder gets sent, red if a second payment reminder gets send and green if the bill was paid.

=AND($G2<>"", $H2="",$I2="")
then format orange
=AND($G2<>"", $H2<>"",$I2="")
RED
=$I2<>""
Green

i'll see if i can work an example in XL2BB up

not sure what you mean by

it obviously all automatically goes with orange since G is not empty in all of them

Book5
	A	B	C	D	E	F	G	H	I	J
1							1st	2nd	paid
2							1	1
3
4							2	1	1
5
6							1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:J6	Expression	=$I2<>""	text	NO
A2:J6	Expression	=AND($G2<>"", $H2<>"",$I2="")	text	NO
A2:J6	Expression	=AND($G2<>"", $H2="",$I2="")	text	NO

 

[randomlychili]

Hi etaf!

Thank you so much for your quick reply! I was actually reading a thing that you had commented on pretty much exactly 9 years ago here ! So pretty cool that you answered here as well

I applied your formulas in German, and they worked perfectly, thank you so much!!

 

[etaf]

I was actually reading a thing that you had commented on pretty much exactly 9 years ago

Wow - time flys by , been here almost 10years now Oct 2012 , playing with Excel/lotus 123/spreadsheets for decades now !!!!!!! 1980's
Anyway,
glad thats solved it for you - very welcome

 

[randomlychili]

Wow - time flys by , been here almost 10years now Oct 2012 , playing with Excel/lotus 123/spreadsheets for decades now !!!!!!! 1980's
Anyway,
glad thats solved it for you - very welcome

amazing! for me it has only been 5 years of Excel, got some catching up to do have a wonderful day!