I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

If the cell to be formatted is B1 and the date to compare to is A1,
then enter this formula in the Conditional Formatting dialog for B1:

=AND(ISBLANK(B1),(NOW()-A1)31)

Set the CF cell format to red, and you should be set. This will cause
B1 to turn red if it is blank and more than 31 days have passed from
the date in A1. Use the Format Painter to copy the format down as far
as you need.

Mark Lincoln

On Jun 22, 2:52 pm, NewGuy wrote:
Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan



"Gary''s Student" wrote:
Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732

"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?- Hide quoted text -

- Show quoted text -

Well, thanks! That almost worked, but from what you gave me, I figured this
out and it works great:

=AND(ISBLANK(B1),(NOW()-31<A1))

Thanks for all your help!!!

- Ethan

"Mark Lincoln" wrote:

If the cell to be formatted is B1 and the date to compare to is A1,
then enter this formula in the Conditional Formatting dialog for B1:

=AND(ISBLANK(B1),(NOW()-A1)31)

Set the CF cell format to red, and you should be set. This will cause
B1 to turn red if it is blank and more than 31 days have passed from
the date in A1. Use the Format Painter to copy the format down as far
as you need.

Mark Lincoln

On Jun 22, 2:52 pm, NewGuy wrote:
Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan



"Gary''s Student" wrote:
Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732

"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?- Hide quoted text -

- Show quoted text -

Ethan, in your original post, you wrote:

I want to see if there is a way to turn
the cell red if no data has been
entered into the cell after a month (31 days).

This tells me that you wish the cell to turn red if it is blank after
31 days have passed. Your formula turns the blank cell red
immediately, and if it remains blank it will remain red until the day
after 31 days have passed, when it will revert to its normal
formatting.

However, if your formula works for you, it works for me! :)

Mark Lincoln

On Jun 22, 3:35 pm, NewGuy wrote:
Well, thanks! That almost worked, but from what you gave me, I figured this
out and it works great:

=AND(ISBLANK(B1),(NOW()-31<A1))

Thanks for all your help!!!

- Ethan



"Mark Lincoln" wrote:
If the cell to be formatted is B1 and the date to compare to is A1,
then enter this formula in the Conditional Formatting dialog for B1:

=AND(ISBLANK(B1),(NOW()-A1)31)

Set the CF cell format to red, and you should be set. This will cause
B1 to turn red if it is blank and more than 31 days have passed from
the date in A1. Use the Format Painter to copy the format down as far
as you need.

Mark Lincoln

On Jun 22, 2:52 pm, NewGuy wrote:
Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:
Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732

"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

In the Conditional Formatting pane

Cell Value is: less than ="Today() - 31"

--
Pops Jackson


"NewGuy" wrote:

Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

Ignore my previous reply. It does not work.
--
Pops Jackson


"NewGuy" wrote:

Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

Ok, new problem:

None of the new cells are referenced to a cell with a date, say the date the
client was added.

My macro currently inserts a row and formats the appropriate cells. I can
easily modify it so it puts the date the client is added into a cell, but if
I set the above conditional formatting into my macro, it only references it
to a set cell. Does that make sense?

I'm caving in on myself here.... :(

"Pops Jackson" wrote:

Ignore my previous reply. It does not work.
--
Pops Jackson


"NewGuy" wrote:

Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

I'm not sure if this is what you're getting at, but in VBA you can refer to
cells by specific address (ex., in C1: "=A1+A2+B1") or by relative address
(same ex: "=RC[-2]+R[1]C[-2]+RC[-1]") To use the relative address, use
..FormulaR1C1 = ...

drhalter

"NewGuy" wrote:

Ok, new problem:

None of the new cells are referenced to a cell with a date, say the date the
client was added.

My macro currently inserts a row and formats the appropriate cells. I can
easily modify it so it puts the date the client is added into a cell, but if
I set the above conditional formatting into my macro, it only references it
to a set cell. Does that make sense?

I'm caving in on myself here.... :(

"Pops Jackson" wrote:

Ignore my previous reply. It does not work.
--
Pops Jackson


"NewGuy" wrote:

Well, here's what I figured out, but it doesn't work. :(

=($A$1NOW())+("")

Any critiques?

- Ethan

"Gary''s Student" wrote:

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED
--
Gary''s Student - gsnu200732


"NewGuy" wrote:

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?