This uses cells in A1 to set the condition for cells in D1:D9 - alter ranges to suit your needs:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" and Sh.Name="Sheet1" Then
Sheets(1).Range("D1:D9").FormatConditions(1).Font.Color = Target.Font.Color
Sheets(1).Range("D1:D9").FormatConditions(1).Interior.Color = Target.Interior.Color
End If
End Sub
After changing the color, you will then have to change the cell by editing it, and pressing enter (so the change event is triggered)
the format conditions are ordered by the order the rules are applied. There are a lot more changes that could be applied, just add another .FormatConditions(1).Whatever = Target.Whatever
to the code
This code does not set up any conditions, it will just alter the one(s) that are there. The conditions are numbered in the rule order displayed on screen
if you don't want to use conditional formatting, and just color the cells, then you can loop over each cell this way:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or Sh.Name <> "Sheet1" Then Exit Sub
' exit if not in key range (formatting key is A:A on sheet1
If VarType(Target) > vbArray Then Exit Sub
' if multiple cells are changed at once, then exit, as i'm not going to fight with multi cell change
Dim TargetRange As Range
Dim lCell As Object
Set TargetRange = Sh.Range("D1:D9")
' changing cells in this area
For Each lCell In TargetRange.Cells
If lCell.Value = Target.Value Then
' only change cells that match the edited cell
lCell.Font.Color = Target.Font.Color
lCell.Interior.Color = Target.Interior.Color
' copy whatever you feel needs to be copied
End If
Next
End Sub
I don't know of any "standard" date/time format built-in to nicely format in this way, so yes your attempt at a custom format seems appropriate.
However, the reason it is not working for you is that time values are not expressed in seconds like you assume - they are stored in a decimal value where 1 is 24 hours.
So you have to use the decimal values equivalent to your desired breakpoints.
Unfortunately, it seems Excel limits the number of breakpoints to three, so I can't get additional breakpoints for "1 minute" (singular), etc. Even if there were more breakpoints, you could never achieve "N hours" because you would need infinite breakpoints (there's no condition that detects a zero minutes component).
So the closest I can get with a custom format is:
[<0.000694]"0 minutes";[<0.041666][m] "minutes";h "hours", m "minutes"
Which gives the following:
00:00:00 0 minutes
00:01:00 1 minutes
00:02:00 2 minutes
00:03:00 3 minutes
01:00:00 1 hours, 0 minutes
01:01:00 1 hours, 1 minutes
02:00:00 2 hours, 0 minutes
02:01:00 2 hours, 1 minutes
01:03:00 1 hours, 3 minutes
23:59:59 23 hours, 59 minutes
I would probably instead suggest writing a VBA function and formatting the field in the formula.
But also, I would suggest you consider whether the value should be a time in the first place. Storing an elapsed period would be better in a specific unit (such as minutes in your case). You still couldn't have infinite breakpoints, but you avoid an obvious problem with the above - it rolls over after 23 hours, 59 minutes.
EDIT: For fun I made a formula which produces your desired results. It's not a format as your question asks for but because that didn't give the desired result exactly, I figured this might be a good alternative:
=if(hour(a1)=0,if(MINUTE(A1)=0,"0 minutes",if(minute(a1)=1,"1 minute",minute(a1) & " minutes")),if(hour(a1)=1,if(MINUTE(A1)=0,"1 hour",if(minute(a1)=1,"1 hour, 1 minute","1 hour, " & minute(a1) & " minutes")),if(MINUTE(A1)=0,hour(A1) & " hours",if(minute(a1)=1,hour(A1) & " hours, 1 minute",hour(A1) & " hours, " & minute(a1) & " minutes"))))
00:00:00 0 minutes
00:01:00 1 minute
00:02:00 2 minutes
00:03:00 3 minutes
01:00:00 1 hour
01:01:00 1 hour, 1 minute
02:00:00 2 hours
02:01:00 2 hours, 1 minute
01:03:00 1 hour, 3 minutes
23:59:59 23 hours, 59 minutes
Best Answer
Go to the cell.
Press CTRL+1 to open the Format Cell.
Go to the Number Tab
Under "Category", select "Custom"
Where it says "Type", enter 0## #### ####
This should ensure that you get your leading zero, and that the spaces are added.
Excel also has a format under the "Special" category just for Phone Numbers. If you click that, then click "Custom" it will insert the Phone format which you can then edit as you see fit.