Spreadsheet formatting
 

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!

Spreadsheet formatting
 

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!

Spreadsheet formatting
 

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

"Joel" wrote:

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!

Spreadsheet formatting
 

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





Philip Drury wrote:

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

"Joel" wrote:

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!


--

Dave Peterson

Spreadsheet formatting
 

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?

"Dave Peterson" wrote:

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





Philip Drury wrote:

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

"Joel" wrote:

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!


--

Dave Peterson

Spreadsheet formatting
 

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

"Philip Drury" wrote:

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

"Joel" wrote:

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!

Spreadsheet formatting
 

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

Philip Drury wrote:

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?

"Dave Peterson" wrote:

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





Philip Drury wrote:

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

"Joel" wrote:

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" wrote:

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(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-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!


--

Dave Peterson


--

Dave Peterson