CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Niffy
Niffy

Reputation: 15

Apply conditional formatting to many rows

I have a data validation set up for a list to choose from (B7:B1000), only two words can be selected: PP or SW. I have figured out a conditional format to fill the row if PP is selected but I need to apply this to the whole spreadsheet. I am having to apply conditional formatting for every row and it's time consuming if I need to do this for 1,000 rows.

I selected Use a formula to determine which cells to format. This is what I currently have it set up as: 

=B7="PP" conditional formatting to be applied to =$F$7:$N$7

I now need to do this for all rows in the spreadsheet: 

=B8="PP" conditional formatting to be applied to =$F$8:$N$8  
=B9="PP" conditional formatting to be applied to =$F$9:$N$9  
=B10="PP" conditional formatting to be applied to =$F$10:$N$10

and so on.

Is there a quicker way rather than manually doing this for 1,000 rows?

I have tried the format painter but its just copies the previous formula. Tried removing the $ symbols but when I click Apply they are re-inserted.

Upvotes: 0

Views: 432

Answers (1)

pnuts
pnuts

Reputation: 59495

Your Title makes me wonder whether you would like some formatting for SW as well as PP but guessing you don't then in Row7 please try:

=$B7="PP"

for the CF formula and make sure Applies to shows =$A$7:$Z$1000 - or whatever column reference you choose in place of Z.

Upvotes: 0

Related Questions