Conditional Formatting
 

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

Enter the following in worksheet 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

This uses column H as an example. If the user enters anything in a cell in
column H, the conditional formats for the entire row are cleared and the
cells in that row are shaded.


This is worksheet code and very easy to install. Just right-click that tab
name near the bottom of the Excel window. Then select View Code. This
brings up the VBA window. Just paste the stuff in and close the VBA window.
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

Start off with green cells/rows, therefore no conditional formatting to
achieve that. You now have 3 conditional formats to work with, one if it is
between 5 and 7 days, one if longer than 7 days, and one if completed.
--
Hth

Kassie Kasselman


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

Thanks, that's great!! Any idea on how to solve the empty cells showing red
before any data is keyed?

"Gary''s Student" wrote:

Enter the following in worksheet 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

This uses column H as an example. If the user enters anything in a cell in
column H, the conditional formats for the entire row are cleared and the
cells in that row are shaded.


This is worksheet code and very easy to install. Just right-click that tab
name near the bottom of the Excel window. Then select View Code. This
brings up the VBA window. Just paste the stuff in and close the VBA window.
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

To fix false reds, change the
Formula Is



For example if you had
=today()-B18
try something like
=(today()-B18)*(B1<"")
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Thanks, that's great!! Any idea on how to solve the empty cells showing red
before any data is keyed?

"Gary''s Student" wrote:

Enter the following in worksheet 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

This uses column H as an example. If the user enters anything in a cell in
column H, the conditional formats for the entire row are cleared and the
cells in that row are shaded.


This is worksheet code and very easy to install. Just right-click that tab
name near the bottom of the Excel window. Then select View Code. This
brings up the VBA window. Just paste the stuff in and close the VBA window.
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

These are the formats I have used:
Condition 1
=AND($A4<=TODAY(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-7)
Condition 3
=AND($A4<=TODAY(),$A4<" ")

Can't thank you enough for your help with this! Can I ask one more thing?
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 - any way to restrict the change to just the
conditional formatting?

Thanks again


"Gary''s Student" wrote:

To fix false reds, change the
Formula Is



For example if you had
=today()-B18
try something like
=(today()-B18)*(B1<"")
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Thanks, that's great!! Any idea on how to solve the empty cells showing red
before any data is keyed?

"Gary''s Student" wrote:

Enter the following in worksheet 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

This uses column H as an example. If the user enters anything in a cell in
column H, the conditional formats for the entire row are cleared and the
cells in that row are shaded.


This is worksheet code and very easy to install. Just right-click that tab
name near the bottom of the Excel window. Then select View Code. This
brings up the VBA window. Just paste the stuff in and close the VBA window.
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!

Conditional Formatting
 

Sorry Philip, that I don't know. Suggest you open a new post.
--
Gary's Student
gsnu200705


"Philip Drury" wrote:

These are the formats I have used:
Condition 1
=AND($A4<=TODAY(),$A4TODAY()-5)
Condition 2
=AND($A4<=TODAY(),$A4TODAY()-7)
Condition 3
=AND($A4<=TODAY(),$A4<" ")

Can't thank you enough for your help with this! Can I ask one more thing?
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 - any way to restrict the change to just the
conditional formatting?

Thanks again


"Gary''s Student" wrote:

To fix false reds, change the
Formula Is



For example if you had
=today()-B18
try something like
=(today()-B18)*(B1<"")
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Thanks, that's great!! Any idea on how to solve the empty cells showing red
before any data is keyed?

"Gary''s Student" wrote:

Enter the following in worksheet 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

This uses column H as an example. If the user enters anything in a cell in
column H, the conditional formats for the entire row are cleared and the
cells in that row are shaded.


This is worksheet code and very easy to install. Just right-click that tab
name near the bottom of the Excel window. Then select View Code. This
brings up the VBA window. Just paste the stuff in and close the VBA window.
--
Gary''s Student
gsnu200705


"Philip Drury" wrote:

Have a spreadsheet that is being used to record customer enquiries, have used
conditional formatting to change the colour of the row if the enquiry goes
over a set amount of days - however, have been asked to change the
spreadsheet so that when the user enters a completion date in the relevant
cell, the row will turn grey and the conditional formatting ignored. The
conditional formatting is set that if the enquiry is within 5 days of todays
date the row is green, if it is between 5 & 7 the row is amber and then it
turns red if the enquiry date is over 7 days - (the downside to this is that
any rows without any data entered are red all the time!) Conditional
formatting will only allow 3 conditions so I am at a loss how to make the row
turn grey when the user enters a completion date?
Can anyone help? - I'm getting desperate!!