Require small help on "Conditional Formatting"

[Ramu2603]

Hello Community,

                             Have prepared an excel sheet where I keep track of ongoing projects. To make it little more interactive I made three conditional formatting rules which are as below:-

           Formula                                             Format                                Applies to

1) $M$4="In Progress"                    "Fill Color = Yellow"                        $B$4:$N$4

2) $M$4="Completed"                     "Fill Color = Green"                        $B$4:$N$4

3) $M$4="Cancelled"                        "Fill Color = Red"                          $B$4:$N$4

Now the hurdle which I am facing is the above mentioned 3 set of rules should get applied to next 500 rows. And with every new row "Formula" should get updated to $M$5, $M$6,... and so on. Secondly, "Applies to" should also get updated accordingly to $B$5:$N$5, $B$6:$N$6,... respectively.

Manually setting up these 3 sets of Conditional Formatting rules for 500 rows individually is very tedious work.

Thanks for support,

[#2 · Accepted solution]

Hi @Ramu2603 

You should format your data as a Table this will allow formulas to auto-adjust as you add new rows:

Then, with the above Table set the following Cond. Format formulas (no $ after $M:(

[#3]

Many thanks @Lz,
This table trick worked.,
Thanks & Regards,
Ram

[#4]

One more request, how do I use this 3 sets of conditional formatting for other Columns as well. For Example I want the cells to change color for Column B,C,D,E respectively

[#5]

@Ramu2603 

For Example I want the cells to change color for Column B,C,D,E respectively

Update the Applies To ranges:

PS: When you get a solution, there's a Mark as response at the botton that helps people who Search - Thanks

[#6]

Thanks once again @Lz, please correct me if I am getting it wrong. "Applies to = $B$4:$E$4", with this condition changes will get applied to only 4th cell of Column B to E.
Whereas my intention is to apply the coloring condition to all cells from $4 to $500 of Column B to E.
I hope you got what I am trying to convey

[#7]

Please carefully review the picture I posted. It doesn't say "Applies to = $B$4:$E$4"

[#8]

Thanks @Lz, overlooked the details..!!

[#9]

@Ramu2603 

overlooked the details..!!

No problem, this happens sometimes ;)

When you have 5 mins I suggest you take a look at Benefits of using Tables instead of Ranges