Conditional Formatting (Date vs Number)

[rclark]

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

 

[Frank Kabel]

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.CFPlus.Download.html

 

[rclark]

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

 

[rclark]

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

 

[rclark]

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

 

[Frank Kabel]

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

 

[rclark]

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

 

[rclark]

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