Conditional Format -[H]:MM

[Preccor]

The formula used for the cell is: =IF((F9-G9)>0,F9-G9,TEXT(ABS(F9-G9),"-[H]:MM"))

Cells F9 & G9 are blank therefore the result of the formula shows "-0:00". How do I conditionally format the cell containing the formula so that the returned value of the formula which is -0:00 - the font color changes to yellow? (-0:00 would be changed to yellow)

A solution would be much appreciated , and quite frankly pretty awesome!

 

[Marcelo Branco]

Re: Conditional Format -[H]:MM - Excel geniuses?

Welcome to Mr Excel forum

You can use the formula-option using the formula below
=AND(F9="",G9="")
Format.. button
Font > Color --> yellow (or Fill-->yellow)

M.

 

[Preccor]

Re: Conditional Format -[H]:MM - Excel geniuses?

Hello Marcelo, thank you for the reply. I cleared rules from the selected cell then entered the formula in the Use formula to determine which cells to format box. Then I formatted the font to yellow. That did not work. I also tried to format with font yellow and fill with yellow. That did not work. Besides clearing rules from selected cells, am I missing a step somewhere? Also, I did not mention in the previous question that I would like a range of cells formatted (The blank cells are F9:F100 and G9:G100. The formatted cells would be H9:H100)). Is this possible? Thank you for taking the time to help solve this issue.

 

[FormR]

Re: Conditional Format -[H]:MM - Excel geniuses?

Hi, another option.

> Conditional Formatting
> New Rule
> Format only cells that contain
> Format only cells with: Cell Value | Equal to | ="-0:00"
> Choose your format

 

[Preccor]

Re: Conditional Format -[H]:MM - Excel geniuses?

Thank you for the reply. I receive an error message that states "The value you entered is not a valid number, time, or string" when i set the cell value to -0:00. The (-) sign must be included because it is a part of the formula =IF((F9-G9)>0,F9-G9,TEXT(ABS(F9-G9),"-[H]:MM")) If I remove the minus sign than the formula does not work for my intended purpose. Any suggestion???? Thanks again.

 

[FormR]

Re: Conditional Format -[H]:MM - Excel geniuses?

Hi, you need to set the cell value to exactly as below (including the = sign and quote marks)

="-0:00"

 

[Preccor]

Re: Conditional Format -[H]:MM - Excel geniuses?

Your attention to this trivial matter is genuinely appreciated. This time it worked. Thank you very much. All best to you.