| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pasting from another spreadsheet changes formatting | Excel Discussion (Misc queries) | |||
| Spreadsheet Appearance/Formatting | Excel Discussion (Misc queries) | |||
| Can just the formatting alone in a spreadsheet be protected? | Excel Discussion (Misc queries) | |||
| Formatting Spreadsheet | Excel Discussion (Misc queries) | |||
| shared spreadsheet - different formatting | Excel Discussion (Misc queries) | |||
Linear Mode
