Home |
Search |
Today's Posts |
|
#1
![]() |
|||
|
|||
![]()
I have a spreadsheet that is calculating intervals for equipment
maintenance. Some of the equipment must be serviced every x months, some of the equipment must be serviced every x hours (of run time). I have all the calculations worked out. The problem I am having is with conditional formatting. In column A I have text that says either "HOURS" or "MONTHS". Based on the value of that cell, I need Column B to be formatted as either a Date (to return the date of the next service) or as a Number (to return the hours till next service). Any ideas on how to do that? Conditional formatting doesnt give the option to format anything except Font, Border and Patterns. I need to format the data type. Thanks, Robbie |
#2
![]() |
|||
|
|||
![]()
Hi
if you need this automatic you'll have to use VBA. You may try the following addin (which allows you this functionality): http://www.xldynamic.com/source/xld.....Download.html -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag oups.com... I have a spreadsheet that is calculating intervals for equipment maintenance. Some of the equipment must be serviced every x months, some of the equipment must be serviced every x hours (of run time). I have all the calculations worked out. The problem I am having is with conditional formatting. In column A I have text that says either "HOURS" or "MONTHS". Based on the value of that cell, I need Column B to be formatted as either a Date (to return the date of the next service) or as a Number (to return the hours till next service). Any ideas on how to do that? Conditional formatting doesnt give the option to format anything except Font, Border and Patterns. I need to format the data type. Thanks, Robbie |
#3
![]() |
|||
|
|||
![]()
I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a plug-in. (Can the plug-in be attached to a sheet to "go along with it") The thing about using code that I'm confused about is how to make it so that it applies to all cells in a column. I want to be able to insert cells and add records randomly and those inserted cells need to carry the same format. I apologize if that is a simple process. I am just a couple of rungs above clueless when it comes to VBA. Thanks again, Robbie |
#4
![]() |
|||
|
|||
![]()
Hi
you can do this with a worksheet_caculate or worksheet_change procedure on your won (see: http://www.cpearson.com/excel/events.htm). But these will probably slow down your spreadsheet significantly (if you have many rows to process) If this goes to other clients you may consider changing your spreadsheet layout a little bit or using a userform four your data entries instead -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag oups.com... I will probably have to use the VBA code. The spreadsheet will need to be distributed to other clients and I dont want to have to add a plug-in. (Can the plug-in be attached to a sheet to "go along with it") The thing about using code that I'm confused about is how to make it so that it applies to all cells in a column. I want to be able to insert cells and add records randomly and those inserted cells need to carry the same format. I apologize if that is a simple process. I am just a couple of rungs above clueless when it comes to VBA. Thanks again, Robbie |
#5
![]() |
|||
|
|||
![]()
This works. Thanks for your help.
Sub FormatNextDue() Dim Cell As Range For Each Cell In Intersect(ActiveSheet.UsedRange, Columns("C:C")) If Cell.Value = "HOURS" Then Cell.Offset(0, 3).NumberFormat = "0.0" If Cell.Value = "MONTHS" Then Cell.Offset(0, 3).NumberFormat = "mmm-yy;@" Next Cell End Sub |
#6
![]() |
|||
|
|||
![]()
I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a plug-in. (Can the plug-in be attached to a sheet to "go along with it") The thing about using code that I'm confused about is how to make it so that it applies to all cells in a column. I want to be able to insert cells and add records randomly and those inserted cells need to carry the same format. I apologize if that is a simple process. I am just a couple of rungs above clueless when it comes to VBA. Thanks again, Robbie |
#7
![]() |
|||
|
|||
![]()
I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a plug-in. (Can the plug-in be attached to a sheet to "go along with it") The thing about using code that I'm confused about is how to make it so that it applies to all cells in a column. I want to be able to insert cells and add records randomly and those inserted cells need to carry the same format. I apologize if that is a simple process. I am just a couple of rungs above clueless when it comes to VBA. Thanks again, Robbie |
#8
![]() |
|||
|
|||
![]()
I will probably have to use the VBA code. The spreadsheet will need to
be distributed to other clients and I dont want to have to add a plug-in. (Can the plug-in be attached to a sheet to "go along with it") The thing about using code that I'm confused about is how to make it so that it applies to all cells in a column. I want to be able to insert cells and add records randomly and those inserted cells need to carry the same format. I apologize if that is a simple process. I am just a couple of rungs above clueless when it comes to VBA. Thanks again, Robbie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional formatting in Charts? | Charts and Charting in Excel | |||
Use Julian Date To Create Serial Number | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
Date Formatting | Excel Discussion (Misc queries) | |||
Adding more than three Conditions to 'Conditional Formatting' | Excel Discussion (Misc queries) |