Another Conditional Formatting Excel 2007conundrum

  TonyV 15:57 17 Oct 2010

Whilst trying to learn the vagaries of CF in Excel 2007, I have managed to get some quite interesting formats going, but am stuck on this one in particular. A column of figures have been formatted to give a red and green background providing certain conditions are met i.e. 50 and >50. This has meant that all the cells are now coloured red which is applicable to 50 and those that are in green refer to those that are >50. Unfortunately, all the cells in the column have not been populated yet, but the "blank" cells are showing red.

What I would like is that since all the cells in Column H (from H10 to H102)have a formula in them which doesn't get populated until other information is added to other cells, can I CF them all to give a no coloured fill until such time as the population takes place? I have to say that following various skirmishes on Google I have tried Various formulae to try and crack it, but it must be something to do with the fact that there are formulae in the cells I want to format that is causing them not to operate correctly.



  VoG II 16:01 17 Oct 2010

Try a CF formula like


  TonyV 16:08 17 Oct 2010

I've tried that one as well others with ISBlank and Number etc, all to no avail. I tried yours in H11 since there is no formula in H10, and it came up no fill in H10, but remains red in all other unpopulated cells where there are formulae in.


  VoG II 16:09 17 Oct 2010

Can you post your formula?

  TonyV 16:39 17 Oct 2010

This is the formula in H11 =IF(D11>0,(E11/G11),0) It follows all the way down to H102. Without the the CF giving red fill, the cells are showing nothing, which is what I want.

The cells are using the CF shown in my previous postings shown here click here


  TonyV 16:40 17 Oct 2010

Forget the last sentence above. That was a different question altogether.


  VoG II 16:52 17 Oct 2010

Presumably you've set it so that zeros don't display. Try this instead then the CF formula should work


  TonyV 17:13 17 Oct 2010

I did that, then applied the =AND(H10<>"",H10<50) formula and all it did was change the balance of unpopulated cells to green instead of red fill. If you like I will send a copy of the file to you via the yellow envelope.



  VoG II 17:18 17 Oct 2010

You'll need to change your green CF formula as well. Something like


  TonyV 17:24 17 Oct 2010

Even the =IF(D11>0,(E11/G11),"") formula. I notice also that if I am not very careful, it is changing other cells that do not need to change. Perhaps if I send the file and you play around with it you will see what is happening!


  TonyV 17:42 17 Oct 2010

That eventually gave me the un-filled unpopulated cells, but it scuppered another cell that I use to get the average number at the bottom of the sheet. At the moment I have there a formula of =IF(H11<0,1,0) which gives 1 or a blank cell, this then applies to all cells that have a number in it which means that there is a figure in the individual fill up mpg column, and therefore by summing these 1's I can get the overall average mpg! It is to do with showing zeros or not. With the above formula's in CF it is filling all the cells with 1 where there should be nothing!

I'll still play about with it and see if I can crack it,



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

iPhone X news: Release date, price, new features & specs

Comment transformer un iPhone en borne Wi-Fi ?