conditional formatting for blank cells

**sallylongden** · 07-04-2012, 07:44 AM

I'm sure that there is a simple solution to this, but I've not yet found it and hoping you can help.

I want to apply conditional formatting to cell I6 if it is blank and cells E6,F6,G6,H6 have a number in one or more of them that is equal to 3,4, or 5

I think I have the start of the formula required :

=AND(<6E6:H6>2,.........
but I seem unable to get the second part.

In anticipation, many thanks for your help.

**martindwilson** · 07-04-2012, 07:59 AM

select i6
insert name define pick a name eg sally (i think its name manager on formula tab)
refers to
=AND(ISBLANK(Sheet1!I6),OR(Sheet1!E6={3,4,5},Sheet1!F6={3,4,5},Sheet1!G6={3,4,5},Sheet1!H6={3,4,5}))
now use conditional format on i6 of =sally

**sallylongden** · 07-04-2012, 09:02 AM

I have managed to get the below line to work for the cell I6 as a conditional format if the below formula is true

=AND(6<$E$6:$H$6>2,ISBLANK($I$6))

The above is all referring to row 6
How do I get it to now transpose all the way down to row 46?

**jason.b75** · 07-04-2012, 09:19 AM

You might want to check your results, that formula will always return true if I6 is blank regardless of the values in the other cells.

Try =AND(ISBLANK($I6),OR($E6:$H6=ROW($3:$5)))

Applied to the whole range you want conditionally formatted.