Drag Microsoft Office Excel Conditional format…

For the Umpteenth number of time, I spent >2 hours to figure out a way to drag my custom format in an incremental way across excel rows.

Here is the user case:

I have an excel spreadsheet that contains columns that look like below:

The custom format that I needed was:

1) Fill Green if value in the cells in B, C, and D is greater than or equal to the value in the cell A for that row.

2) Fill Yellow if value in the cells in B, C, and D is less than the value in the cell A for that row.

Exact Requirement: I want to create the formatting for the cells in one row, drag it down and expect Excel to do the incremental adjustments to the cell values as needed.

By default when I create the formula using the “Conditional Formatting” option it creates something like this:

If I “Format Paint” other cells then the “Cell Vale < $C$1” remains static. I wanted it to change based on the row it is on.

Fix was simple (I think other better ways too!):

1) In the formula remove the $ from the “Cell Value…” for the value that needs to reflect the changes. When I updated the formula like below I was able to format paint it over other cells:

In retrospect, that was simple…