Spreadsheet formatting

[Philip Drury]

I have a spreadsheet that has been built for our Customer Service unit to
record enquiries - they asked for the spreadsheet to change the row column
depending on how old the enquiry had got before it was completed. For this I
have used the following conditional formatting:

Condition 1
=AND($A4<=TODAY(),$A4>TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4>TODAY()-7)
Condition 3
=AND($A4<=TODAY(),$A4<>" ")

What they also want is that the row turns grey once they input a completion
date - this is done with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("H:H"), Target) Is Nothing Then
Exit Sub
End If
If IsEmpty(Target.Value) Then
Exit Sub
End If
Target.EntireRow.ClearFormats
Target.EntireRow.Interior.ColorIndex = 15
End Sub

However, and this is where I need some help, the code that turns the
completed rows grey also knocks out the cell formatting i.e. cell alignment
and the fact that some cells are formatted specifically as dates - is there
any way to restrict the change to just the conditional formatting?

Also, any idea why (or how to stop the fact that) the empty rows sit on the
spread sheet coloured red?

If you can help me with htis - you're a star!

 

[Joel]

I would just eliminate this one statement.

Target.EntireRow.ClearFormats

this routine should only change the color and nothing else

You also may want to change the patttern
Target.EntireRow.Interior.Pattern = xlSolid

 

[Philip Drury]

Trouble is that if I remove that line the Conditional Formatting over-rides
the code so the line stays the 'traffic signal' colours

 

[Dave Peterson]

So just remove the conditional formatting:
target.entirerow.FormatConditions.Delete

 

[Philip Drury]

That's great - works perfectly.

Do you know why the 'empty' rows show red until they have data input, is
there a problem with the conditional formatting that I have missed?

 

[Joel]

You can find all the conditional format by doint the following
1) On the Edit menu select go to
2) Press Special
3) Select Conditional formating

 

[Dave Peterson]

When you selected the cell and did format|conditional formatting, was there
anything left hanging around?