VBA Conditional Formatting Number to Custom Date/Time Format

[Jimikar]

Hi there everybody.

Here is the situation.
I have a spreadsheet that in particular cells I have assigned a Formula via VBA coding. I managed to have the sheet calculated every sec, so the value of those cells changes every sec.

Now my problem is this.
I want to Conditional Format those cells (which are in Number Format) to a Custom Format I made (no font or color formatting, its a custom Date/Time format of type "ss \s","mm \m ss \s" etc). I need this to be done via VBA code because those cells get deleted and reassigned due to user updating procedure.

When I tried to record my actions, I got a macro with "ExecuteExcel4Macro" in it, which of course doesn't work when trying running the macro.

This is the macro Excel recorded:

Range("C11:D22").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0,007" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
ExecuteExcel4Macro "(2,1,"" ss s"")"
Selection.FormatConditions(1).StopIfTrue = True

Does anybody has an idea how to solve this? I googled the entire Internet and found nothing helpful.

 

[Jimikar]

Boys and Girls, I worked around this problem and found another solution.
Still I would like to read your suggestions to it.

 

[VoG]

I'm a bit confused about which custom format you want to use. Did you try something like?

Range("C11:D22").NumberFormat = "ss \s"

 

[Jimikar]

Yeap, but I want it as a conditional format cause the cells contain formula which are recalculated every 1 sec and I have 4 types of format I want to use, in this priority:

1: ss \s
2: mm \m ss \s
3: hh \h mm\m ss\s
4: dd \d\a\y\s hh \h mm\m ss\s

 

[JamieEmmart]

Jimikar, I also have this problem. I am trying to format numbers with text "wks" or "months" at end depending on another value in that row. How did you work around the ExecuteExcel4Macro issue?

 

[Peter_SSs]

@JamieEmmart
Welcome to the MrExcel board!

This thread is 15 years old and Jimikar has not been back to the forum since then.
I suggest that you start a new thread of your own and set out the details & examples of what you have and what you want to achieve. You can put a link back to this thread if you think that would assist your potential helpers. In relation to providing samples I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.