Custom number formatting

[MiniFav]

I understand you can highlight text using the custom number formatting.
I am looking to display a number of hours followed by minutes. I have tried [H]:MM but this seems to be limited by a length which i will exceed.

so I have reverted to using decimals. However this allows the the two decimal spaces to go above 59, so i would like a highlight to grab my attention when a value gets entered which exceeds this. Eg. 123456.78 would be highlighted where 123456.55 would be fine.

I have tried conditional formatting but with the size of the spreadsheet i am getting a lot of slow down every time i enter values.

I understand how to implement VBA code so I am open to this if the number formatting route is not the best way to go.

Any suggestions would be great.

 

[mole999]

what sort of hours are you looking for ?

 

[MiniFav]

max hours could be HHHHHHHH:MM for example but could also be as low as 00:00

 

[Ricky918]

Have you given this a try?

ActiveCell.NumberFormat = "##"":""##"":""##"":""##"

 

[mole999]

there is a finite limit to the hours 9999:59:59. after that it has to be text values > https://support.microsoft.com/en-us/help/214386/maximum-times-in-microsoft-excel