Conditional formatting with multiple condtitions

[bbartlinski]

I need to change the color of a row based on what cell has data in it. I've made multiple conditional formatting rules and it works on one row. How can I apply the rules to the entire sheet based off each individual row...

The orange row has the rule applied but if I copy it to the yellow row it grabs the data from the orange row now the yellow. I dragged it down using the "Fill Formatting only" parameters and did the same thing.

View attachment 70183

 

[Joe4]

I have never had a whole lot of luck trying to copy/extend Conditional Formatting (it can be done, but tends to be a bit tricky).
I have much better luck taking the following approach:

1. Select the ENTIRE range that you want to apply the Conditional Formatting to
2. Enter the Conditional Formatting rule (if a formula, write the formula as it applies to the very first cell in your selected range)
3. Choose your formatting option
4. Repeat steps 2-3 for all the other Conditional Formatting rules you want

If you use absolute/relative references in your Conditional Formatting Rules correctly, Excel will automatically adjust the formulas for the other cells in your selected range.

For example, let's say that we want to format rows 2-10, and want to highlight the ENTIRE row if column B of that row is "Yes".
Then we would do the following:
1. Select rows 2-10
2. Enter the following Conditional Formatting formula:

=$B2="Yes"

Note the "$" in front of the column reference locks it down so that EVERY cell in the row will look at column B.
But we did NOT put a "$" in front of the row, so that all cells in row 2 will look at B2, and all the cells in row 3 will look at B3, etc.
3. Choose your desired formatting option