Highlight row if date is this week

[RECrerar]

Hi

I'm sure this is really easy but am a bit confused where to start.

I have a spreadsheet of tasks that need completing and have a couple of questions.

1. In one column (j) there is a date for review. What I would like is to be able to highlight the row as far as the last non-empty cell if that date is in the current week with one colour and with another colour if that date is in the past.

2. If text is added to a in cell column H, todays date will automatically be added to column I.

Thanks in advance for any help, Robyn

[Bob Phillips]

1. Use conditional formatting

To create this highlighting, follow these steps:

* Step 1.
Select all of the target rows, starting in column A, row 2.


* Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(A2<>"",$H2>=TODAY()-CHOOSE(WEEKDAY(H2),0,1,2,3,4,5,6),$H2<TODAY()+CHOOSE(WEEKDAY(H2),7,6,5,4,3, 2,1))
Click the Format button
Select the Pattern Tab
Select first colour from the palette
OK


* Step 3.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(A2<>"",$H2<TODAY())
Click the Format button
Select the Pattern Tab
Select second colour from the palette
OK
OK

2. VBA

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H:H" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Date
Else
.Offset(0, 1).Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[RECrerar]

The code for entering the date works perfectly, however am having some problems with the conditional formatting.

The target date is in column J and the first row of data in the table is Row 5 so i Modifies the formula as follows:

=AND(A5<>"",$J2>=TODAY()-CHOOSE(WEEKDAY(J2),0,1,2,3,4,5,6),$J2<TODAY()+CHOOSE(WEEKDAY(J2),7,6,5,4,3, 2,1))

the result I got was that some cells in column A were highlighted and the rest of the row not, and the fianl row was highlighted. Also it seemed to be highlighting the cells at random, as some of the dates were clearly not this week.

Also I didn't mention this before but I already have some conditional formatting in two columns to showthe priority of the tasks, is there a way that this can only be over written if the date formatting is true?

[RECrerar]

Hi

I have writen the following code in the workbook open sub, which seems to work and doesn't overwrite the conditional formatting. I will look into writing functions into conditional formatting but don't really understand it yet.

[vba]Private Sub Workbook_Open()
Dim row As Single
Dim Seven As Date
Dim Today As Variant
Today = Date
row = 5
Seven = DateAdd("ww", 1, Today)
Do Until Cells(row, 1).Value = ""
If Cells(row, 10).Value < Seven Then
Range(Cells(row, 1), Cells(row, 10)).Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
If Cells(row, 10).Value <> "" And Cells(row, 10).Value < Date Then
Range(Cells(row, 1), Cells(row, 10)).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
End If
row = row + 1
Loop
End Sub[/vba]

Thanks for your help, am using your code to automatically enter the date, but unless there is any reason why the above approach is not good, I will use that for the highlighting

[Bob Phillips]

The code for entering the date works perfectly, however am having some problems with the conditional formatting.

The target date is in column J and the first row of data in the table is Row 5 so i Modifies the formula as follows:

=AND(A5<>"",$J2>=TODAY()-CHOOSE(WEEKDAY(J2),0,1,2,3,4,5,6),$J2<TODAY()+CHOOSE(WEEKDAY(J2),7,6,5,4,3, 2,1))

the result I got was that some cells in column A were highlighted and the rest of the row not, and the fianl row was highlighted. Also it seemed to be highlighting the cells at random, as some of the dates were clearly not this week.

Also I didn't mention this before but I already have some conditional formatting in two columns to showthe priority of the tasks, is there a way that this can only be over written if the date formatting is true?

All references to the date cell have to be absolute column, that is $J2 not J2.

[Bob Phillips]

Couple of errors in my text as well

* Step 1.
Select all of the target rows, starting in column A, row 2.


* Step 2.
Goto Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of
=AND(A2<>"",$H2>=TODAY()-CHOOSE(WEEKDAY(H2),0,1,2,3,4,5,6),$H2<TODAY()+CHOOSE(WEEKDAY(H2),7,6,5,4,3, 2,1))
Click the Format button
Select the Pattern Tab
Select first colour from the palette
OK


* Step 3.
Click the Add>> button (to get another condition)
Change Condition 2 to Formula Is
Add a formula of
=AND(A2<>"",$H2<TODAY())
Click the Format button
Select the Pattern Tab
Select second colour from the palette
OK
OK

[RECrerar]

Oh that would make sense.

Will have another look at it when I get a chance

[Bob Phillips]

Re the other CF, make the date tasks fisrt tests.

[RECrerar]

Thanks, I'd guessed that was the way to do it, but not tested it yet

[RECrerar]

Hi again.

Okay have tried your formulas making sure to have the absolute column references, and have just changed A2 to A5 and H2 to J5 as explained before.

When I select okay the highlighted but he majority only have the first cell highlighted rather than the whole row. I'm assumning it doesn't like blank cells in the rows but many of my rows don't have any blank cells, any ideas why this is happening?

[RECrerar]

Hey, Right I have now got it working in that it highlights the whole row, having said I had but in all the right $ signs I checked again and realissed i had not.

A couple more questions now.

1. For some reason it does not appear to believe that 28/09/07 is this week, although it does recognise 27/09/07 and 01/10/07. There are no numbers missing in the sequences as far as I can tell.

2.How Can I add more than 3 conditional formats? I would want 4 row conditions in the same range and a further 2 applying to cells in columns E and F only.

Thanks again, and sorry if I'm being a bit slow

[Bob Phillips]

I think you are also getting your row references mixed up.

I just looked again at youyr formula, and you have A5 and J2. Besides being $J2, the row numbers should be the same.

The formula depends upon three things:
- the column of the date check cell being fixed
- the column of the other check cell NOT being fixed
- the other check cell entered being the reference of the active cell

[Bob Phillips]

http://www.xldynamic.com/source/xld.....Download.html

[RECrerar]

Update:

Have managed to get the column and row conditional formats in, so that's all good, the question of whether or not it is possible to get more than three conditional formats in the same range still stands.

have curently got the sheet in an acceptable but not perfect format, but since it is just a sheet to tell me what work I have to do, it will do for now. Thanks for your help

[RECrerar]

oh excellent, thank-you, sorry posted without refreshing the screen first.

The Addin is fantastic, works perfectly. Thank-you very much