From your screenshot, I do not know when "New Hire" and those column should be highlighted.
I think, if you are new to excel, you may not have the ideas of "Relative Reference", "Absolute Reference" and "Mixed Reference".
Using Contional Formatting, in the rules, you can use the mixed reference address. For example, I guess you want to highlight Column M whenever Column L is "Transfer". Your conditional formatting rule in Column M can be do it in this way:
- Select M2 (The cell at row 2 in column M)
- Home > Styles > Conditional Formatting > Manage Rules
- New Rule
- "Use a formula to determine which cells to format" (you probably have done it)
- This step is one of the key that you need to know. You can type the following formula
=$L2="Transfer"
The above formula is using Mixed reference address.
- Apply the format you want
- Click ok button
- Now, you should be still in Conditional Formatting Rules Manager dialog. You can see "Applies to", you can select the cells that you want the rule applies to, i.e. $M$2:$M$100.
- click ok.
In the above steps, step 5 and 8 are related to each other. In step 5, the formula locks Column L, but not locks on rows. If that formula apply to another row, e.g. applying on M3, the formula will actually becomes
=$L3="Transfer"
and now, step 8 says that the rule (you may think of the formula) is going to apply on M2 to M100. The rule will have adjustment according to the rows. Hence, 1 conditional formatting rule can apply to 99 cells in column M from row 2 to row 100.
You try to update your rules to see if it is the "less footsteps" approach.
