PDA

View Full Version : Solved: Conditional Formatting only when value is present



Henry
02-01-2011, 02:14 PM
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
02-01-2011, 04:12 PM
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
02-01-2011, 04:35 PM
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
02-01-2011, 04:54 PM
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
02-01-2011, 07:51 PM
Perfect :thumb I actually tried using LEN in some convoluted way that just didn't work. Thank you for your help !

Trebor76
02-01-2011, 08:47 PM
You're welcome. I'm glad it's all sorted :pleased: