excel 2010 - Conditional formatting, entire row based

Question

I've searched and read through answers related to conditional formatting, but I can't seem to get mine to work, so maybe I'm doing something wrong.

I have a worksheet for work. It contains a list of animals in our shelter. What I'm attempting to do is color the entire row green if they've been adopted (noted by an "X" in column "G"). I've had =$G$2="X" and =$G2="X", but neither work. It'll only color the one row that was active when I set the rule, and when I enter "X" in another row, it does nothing. What am I missing?

Answer 1

Use the "indirect" function on conditional formatting.

1. Select Conditional Formatting
2. Select New Rule
3. Select "Use a Formula to determine which cells to format"
4. Enter the Formula, =INDIRECT("g"&ROW())="X"
5. Enter the Format you want (text color, fill color, etc).
6. Select OK to save the new format
7. Open "Manage Rules" in Conditional Formatting
8. Select "This Worksheet" if you can't see your new rule.
9. In the "Applies to" box of your new rule, enter =$A$1:$Z$1500 (or however wide/long you want the conditional formatting to extend depending on your worksheet)

For every row in the G column that has an X, it will now turn to the format you specified. If there isn't an X in the column, the row won't be formatted.

You can repeat this to do multiple row formatting depending on a column value. Just change either the g column or x specific text in the formula and set different formats.

For example, if you add a new rule with the formula, =INDIRECT("h"&ROW())="CAR", then it will format every row that has CAR in the H Column as the format you specified.