Conditional Formatting (Date vs Number)
 

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

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

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

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

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

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

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

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