Extend conditional formatting to the entire table

Hello everyone and thanks in advance for your help.

Here’s my problem (I’m working on Excel 2010):

I’m trying to format several cells (with green fill) based on the result obtained in another cell. So far, I’m using the following method:

1. I select the cells that I want to format (specifically A4, B4, C4, D4, E4, G4, I4, J4).

2. I use the "Conditional Formatting" function => "New Rule" and select the option "Use a formula to determine which cells to format"

3. I type the following formula: =AND($F$4="yes") and select the green fill.

Indeed, when I type "Yes" in F4, the cells A4, B4, C4... up to J4 turn green. So far, so good.

However, I want to extend the formula to my entire table. Let me explain, I want for F5="Yes" A5, B5...J5 to turn green and likewise for rows 6, 7, 8, etc.

I tried the same approach as above by selecting all the cells at once and replacing the formula with =AND($F:$F="yes"). But it doesn't work.

Please tell me there is a method that saves me from having to copy my formula for each line.

Thanks for your help

Nico