Excel 2010 Conditional Formatting -

[PWD]

Good morning, I've searched this forum and can't find the answer but I'm sure it's out there!

Basically, I've inherited a workbook and want to insert Condition Formatting only if a cell contains data but it seems to be applying it anyway. Is it because the cell already contains a formula, "=IF(ISERROR(SUM(I6:W6)/G6),"",SUM(I6:W6)/G6)"?

Many thanks,
D€$

 

[PWD]

Well I added a new rule "Cell Value > 0" | No Format Set and then Stop If True and that appears to have it sorted. Odd though.

Many thanks,
D€$

 

[PWD]

No, that's not right.

Many thanks,
D€$

 

[zelgar]

Conditional Formatting is depenent on the formula for the Conditional Format, not the formula in the cell. Also, you can have multiple Conditional Formatting formulae with different formulae that can overlap (e.g., if both formulae are met). Without knowing what you want in the Conditional Format, it's hard for anyone to help you. Please indicate what you want your Conditinal Format to do.

 

[PWD]

I get you. OK, I have a template that has to contain all the rows that may be reported on in any given month - Rows 6:13. If there's no data for this month I just want the cell to have no fill. If the cell (H6) contains data then colour it in green. The cell's data come from a SUM of other columns in that row. As soon as I create a rule, "H6 > 0", it colours it in green, which is what I don't want.

Many thanks,
D€$

 

[SkipVought]

Generally, you ought not to put formulas in rows that do not contain data, like row 6 where G6 is empty!

Excel 2007+ makes it easier to avoid this condition, if you were to employ the Structured Table feature, where table formulas automatically get propagated as you add data/rows to a table.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

Change your rule to H6<>

 

[PWD]

Thanks for the replies. The rule change did what I needed.

@Skip, I had a look at converting to a table but it appeared to change the formatting etc. There will be the same number of rows every month as I think the end user will be collating the results and wants to be able to compare like with like.

Is there any sensible way of avoiding loads of conditions? I can't think that there will as I need to compare each row to determine the colour. H6 with F6, H7 with F7, etc.

Many thanks,
D€$