Solved: Conditional Formatting only when value is present

[Henry]

Hi

I've run into a problem that I haven't been able to find a solution for yet and was hoping someone could help me with it. I have worksheet with 12 columns with the data arranged in rows. For each row, there may be 2 to 12 entries. I'm trying to use conditional formatting to fill the cells of the highest (green) and lowest (red) values, but only for those cells that contain data. Data is numeric only.
Right now, it works once I enter the values, but blank rows are all filled with green and values of 0 result in that cell and every other blank cell in the row to be filled with red.
I am using the cell value equal to =MAX($A11:$L11) for the highest value and =MIN($A11:$L11) for the lowest, in that order.
Do I need to resort to VBA for this ?

Thanks in advance !

[Trebor76]

Hi Henry,

Welcome to the forum!!

Have a look at the attached where I've added another condition where if the cell is blank (value equals zero) there's no formatting.

HTH

Robert

[Henry]

Robert

Thank you for the quick reply. The only shortcoming of that method is the value of 0 does not get filled with red, even though it is the lowest value of the data entered. Still, this solution works better than what I started with !

[Trebor76]

Ah, good point!!

Have a look at the attached then where I've put in a formula for the first CF condition.

HTH

Robert

[Henry]

Perfect I actually tried using LEN in some convoluted way that just didn't work. Thank you for your help !

[Trebor76]

You're welcome. I'm glad it's all sorted