Could someone please help

  • Thread starter Thread starter Russell McDade
  • Start date Start date
R

Russell McDade

I was wondering if someone could help me. I don't use excel too much and I
need some help. What I want to do is to highlight a row if a date hasn't
been entered after one week. For example; we have a spreadsheet that is our
'Die-Register', and every day we have over twenty dies that move through our
system, each die is recorded as a new row in the spreadsheet. In each row we
record the die number, the date it was issued, to whom it was issued, blah
blah blah, and finally the date it was returned.
What I wanted to do was to highlight any die [or row] that had been issued
but *not* returned after one week, is it possible to write a macro or
something to do this in excel? I would appreciate any help with this. TIA

Russell
 
Russell McDade said:
I was wondering if someone could help me. I don't use excel too much and I
need some help. What I want to do is to highlight a row if a date hasn't
been entered after one week. For example; we have a spreadsheet that is our
'Die-Register', and every day we have over twenty dies that move through our
system, each die is recorded as a new row in the spreadsheet. In each row we
record the die number, the date it was issued, to whom it was issued, blah
blah blah, and finally the date it was returned.
What I wanted to do was to highlight any die [or row] that had been issued
but *not* returned after one week, is it possible to write a macro or
something to do this in excel? I would appreciate any help with this. TIA

Russell
Click to expand...


P.S. we are only using Office 97
 
This can be done without a macro through the use of conditiona
formatting.

Look in the format menu, conditional formatting.

Without knowing the excat layout of your data, I can only provide
general solution.


Select the cells in the first row that you want highlighted , and go t
the conditional formatiing control.
Select the formula option.
enter the formula =TODAY()-B2>7
Please note in this formula I have assumed the date to test for is i
cell B2, you will probably need to change it to suit your setup.
Now select the format you want applied when the die is overdue.
Then press OK.
Then copy the highligted cell and paste THE FORMAT ONLY over all th
other cells.

That should do it.

If it is not clear please ask for more detail
 
Kieran said:
This can be done without a macro through the use of conditional
formatting.

Look in the format menu, conditional formatting.

Without knowing the excat layout of your data, I can only provide a
general solution.


Select the cells in the first row that you want highlighted , and go to
the conditional formatiing control.
Select the formula option.
enter the formula =TODAY()-B2>7
Please note in this formula I have assumed the date to test for is in
cell B2, you will probably need to change it to suit your setup.
Now select the format you want applied when the die is overdue.
Then press OK.
Then copy the highligted cell and paste THE FORMAT ONLY over all the
other cells.

That should do it.

If it is not clear please ask for more detail.
Click to expand...


Thanks so much for taking the time to help me but I could not get
it to work because I have not made my requirements clear, so I have
posted it here so you can see what I mean:
http://members.optusnet.com.au/~wirecut/forkieran.htm

What I would like is something that can highlight a row or date, based on
the following criteria, using my example:
i.e. Column 'B' is called 'Issue Date' and column 'I' is called 'Returned
Date'.
Row #4 shows that Die # R0379 was issued on 21-Dec-03 but it has nothing
entered in Column 'I' [Returned Date]. Is there a way to highlight this row
or
cell-i4 after one week after the date entered at cell-b4?
I need a formula or macro that will highlight a row if nothing is entered in
one week after the date entered in column 'B' [Issued Date]

Thanks again Kieran for you time I appreciate it.

Russell
 
Put the following in column K4 then copy it in col K to all the
populated rows.. I doesn't highlight, but will tag the overdue dies.

=IF(I4,"",IF(B4+7<TODAY(),"OVERDUE",""))



Kieran said:
This can be done without a macro through the use of conditional
formatting.

Look in the format menu, conditional formatting.

Without knowing the excat layout of your data, I can only provide a
general solution.


Select the cells in the first row that you want highlighted , and go to
the conditional formatiing control.
Select the formula option.
enter the formula =TODAY()-B2>7
Please note in this formula I have assumed the date to test for is in
cell B2, you will probably need to change it to suit your setup.
Now select the format you want applied when the die is overdue.
Then press OK.
Then copy the highligted cell and paste THE FORMAT ONLY over all the
other cells.

That should do it.

If it is not clear please ask for more detail.
Click to expand...


Thanks so much for taking the time to help me but I could not get
it to work because I have not made my requirements clear, so I have
posted it here so you can see what I mean:
http://members.optusnet.com.au/~wirecut/forkieran.htm

What I would like is something that can highlight a row or date, based on
the following criteria, using my example:
i.e. Column 'B' is called 'Issue Date' and column 'I' is called 'Returned
Date'.
Row #4 shows that Die # R0379 was issued on 21-Dec-03 but it has nothing
entered in Column 'I' [Returned Date]. Is there a way to highlight this row
or
cell-i4 after one week after the date entered at cell-b4?
I need a formula or macro that will highlight a row if nothing is entered in
one week after the date entered in column 'B' [Issued Date]

Thanks again Kieran for you time I appreciate it.

Russell
Click to expand...
 
Using your spreadsheet, highlight/select cells a2 to J2.
Then go to formatting > conditional formatting and enter the following
(as a formlua option)
=AND(TODAY()-$B2>7,$I2=0, $B2>0)

then select the format you want to see by use of the format button.
then press OK.

Now copy the range a2:j2 and PASTE FORMAT ONLY over the rest of your
data range. The formula allows for blank rows, so make allowances for
growth in your lists.

That should do it.

Hope it helps.
 
Kieran said:
Using your spreadsheet, highlight/select cells a2 to J2.
Then go to formatting > conditional formatting and enter the following
(as a formlua option)
=AND(TODAY()-$B2>7,$I2=0, $B2>0)

then select the format you want to see by use of the format button.
then press OK.

Now copy the range a2:j2 and PASTE FORMAT ONLY over the rest of your
data range. The formula allows for blank rows, so make allowances for
growth in your lists.

That should do it.

Hope it helps.
Click to expand...


Thanks Kieran for your time and trouble. The spreadsheet
works exactly as required.
Russell

P.S. I will have a look at the formula, it looks a bit like JavaScript.
Where can I learn it?
Russell
 
Back
Top