Conditional Format Multiple Rows

[John Caines]

Hello All;; (I see we've gone green!

I'm trying to speed up a way of conditional formatting rows of data in excel 2010.

Example;
I have say 30 rows of data.
Range is B5:I34
So 30 rows,,

Here's my issue. Say for row (B5:I5) ,, the column H shows just the word 'YES or NO'
So,, if H5 says the word YES I want to format the whole row (2 shades of yellow.)

OK,, I know how to do this,, go to conditional formatting (Whilst I've selected cell range B5:I5/Then select "Use Formula to determine which cells to format",,, enter =$H5="YES" then just go to format,, and select colours,, then click apply.

Great!!
But,,, do I really have to do this for every row?
I mean,, is there a way to somehow copy/paste formula or something,, or use 1 formula, 1 condition for all the rows?

Otherwise I have to repeat this action 30 times,, 1 by 1.

there must be quick way to do this for all 30 rows?

I just checked youtube,, but just found how to condition 1 row (Which I can do anyway,, but how can I do this for all the rows,, so I don't do it 30 times?

I hope the above makes sense.
(Just to add,, if I was at row 18,,, the 'Yes' 'No' cell would be H18,,, to format from B18:I18

I hope somebody can advise.

Many Thanks
JC.
I like the green!!! Eco Friendly!

 

[Skybluekid]

Hightlight B5:I5

If you click on the square box on the bottom of I5 and drag down, that will pull the conditional format with it.

 

[John Caines]

Skybluekid!!
Many thanks,,

I feel a bit stupid,, I know I've done this before,, it was a while ago,,, I just forgot...

I still went and done it 30 times!!
But,, just tried your solution,, works great...!!!

Again many thanks,
I won't forget this again I can assure you!
All the best
JC