[Closed] Conditional Formatting - applying to all rows?

Have you tried applying the conditional formatting according to a formula?

and dont forget to consider the tick boxes that say 'end if true'

Formula in your conditional formatting - has to come out "true" for it to apply.

You might need to use some absolutes, (e.g. $A2 <> "") but after that you should be able to use the auto fill.

Select the cell where it works then from the ribbon go to Conditional Formatting/Manage Rules.  Make sure the top bit says "show formatting rules for Current Selection" then on the rule you want to copy change the cell range in the "Applies To" box.

Here's the three formulas I'm using and what they apply to and they're in this order for row 3:

TURN ROW RED IF 'YES' SRLECTED IN COL I.

=$I$3="Yes.

Applies to: =$A$3:$3:$B$3,$G$3:$H$H$3    STOP IF TRUE

TURN ROW GREEN WHEN COL J POPULATED.

=NOT(ISBLANK($J$3)).

Applies to: =$A$3:$3:$B$3,$G$3:$H$H$3    STOP IF TRUE

TURN CELLS YELLOW WHEN POPULATED.

==NOT(ISBLANK(A3))

Applies to: =$A$3:$3:$B$3,$G$3:$H$H$3

1st formula - $I$3 -> $I3

2nd formula $J$3 -> $J3

Last one is OK

TURN CELLS YELLOW WHEN POPULATED.

==NOT(ISBLANK(A3))

Applies to: =$A$3:$3:$B$3,$G$3:$H$H$3

I think your cell references could be simplified

As an example, maybe try replacing the above with

Formula

=AND(COUNTA($A1:$H1)>0,ROW()>1)

Applies to

=$A:$H

BTW I included the ROW()>1 so the top row wouldn't be formatted as I assumed this would be your headings but it can be removed or adjusted.

The important part I think is fixing the column e.g. $A1 and not the column and row e.g. $A$1 since you want the format to be applied to all rows

and I'm no Excel expert, others may know way better

thepurist is correct. You need to remove the absolute references in the formula, then extend the range it applies to, to all cells.

thepurist is giving you the straight answer so

TURN ROW RED IF ‘YES’ SRLECTED IN COL I.

=$I$3=”Yes.

Applies to: =$A$3:$3:$B$3,$G$3:$H$H$3 STOP IF TRUE

could be changed to

=$I3=”Yes"

Applies to: =$A:$H STOP IF TRUE

and include my row check if you want some header rows or go for a specific range like $A$3:$H$250 if you really want to

If you are applying the forumula to different columns rather than different rows, then it needs to be:

=I$3="Yes"

Otherwise, when you apply across other columns, it will keep referring to column I.

Don't apply it A:H either, do A3:H3, otherwise all the cells below row 3 will colour according to the value in row 3.

Thank you all for taking the time to reply. Im going to try and apply your solutions later so hopefully you can do more fun stuff!

Thanks again.

Ignore my last point, jus realised you wanted it across all rows so the A:H will work, as long as you only care about what is in row 3 when formatting the colour.

I've tried $I$3->$I3 but sadly brings an error message. What does the -> do?

The table is column A - K.

Customers populate columns A - I.

After they populate a cell it turns yellow.

If they select Yes in column 'I' then cells A - I (only in that row) turn Red. If 'No' then cells remain yellow.

Finally two of my colleagues will populate cell in column J when they've done their side of things. Doing this will turn the entire row green UNLESS  cell in column 'I' is "Yes" whereby the row remains all Red to show it as a priority.

I've managed this on one line but it's the extending to all rows below that is puzzling me.

Thanks again for your help and patience.

Just remove the $ before the row references in all.formulas.

Thank you all! Removing the $ row reference was the last bit of the solution.