86

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?

Filburt
  • 17,626
  • 12
  • 64
  • 115
Josh
  • 3,225
  • 7
  • 30
  • 44

6 Answers6

146

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.

Liam
  • 27,717
  • 28
  • 128
  • 190
John
  • 1,477
  • 2
  • 9
  • 2
  • 3
    This is probably a newbie error, but if your column contains a number (not a letter like X), then you need to remove the quotes around the number to make this work - e.g. =INDIRECT("T"&ROW())=5 – Coruscate5 Jul 13 '17 at 00:49
  • One of the easiest and best answer i have seen for highlighting the row. – Prabesh Sep 15 '17 at 15:36
  • This answer worked for me using Excel 2013. The accepted answer made no sense and therefore I could not find any way to make it work. With this answer I even added the MAX function so that it will highlight the whole row of the max value in column g (b in my case). And if the values change, the highlight will follow the max. – Nelda.techspiress Nov 09 '17 at 19:09
  • You're genius mate. – J. Wrong May 20 '18 at 02:28
109
=$G1="X"

would be the correct (and easiest) method. Just select the entire sheet first, as conditional formatting only works on selected cells. I just tried it and it works perfectly. You must start at G1 rather than G2 otherwise it will offset the conditional formatting by a row.

Liam
  • 27,717
  • 28
  • 128
  • 190
Ryan T
  • 1,122
  • 1
  • 7
  • 2
  • 4
    This one little sentence made all the difference: `Just select the entire sheet first, as conditional formatting only works on selected cells.` – HPWD Aug 29 '13 at 20:09
  • 1
    @pnuts: I think that is the `Select "Use a Formula to determine which cells to format"` line which did a diff there for many people coming from outside. The OP still got this, but not all of us :) – Mat M May 09 '14 at 12:56
  • 3
    Actually, you just have to make **a** selection for this to work. You may apply CF to a small table in a worksheet. The relative setting applies to the selection's top row, e.g. if top row is 5 and you want to test column G in the current row for coloring, it will be `$G5`. – Mat M May 09 '14 at 12:59
  • 4
    This one fixed it for me `You must start at G1 rather than G2 otherwise it will offset the conditional formatting by a row`. – jwatts1980 Oct 30 '16 at 22:49
  • 1
    But this only works for a single row, what if I have 200... do I need 200 rules? – gunslingor Jul 25 '17 at 13:02
  • 1
    @gunslingor - it will apply to all the rows you've selected before clicking the conditional formatting button. You can verify on the Conditional Formatting Rules Manager the "Applies to" field is set to $[first column]:$[last column], e.g., =$G:$J – WhyGeeEx Nov 09 '18 at 14:52
9

To set Conditional Formatting for an ENTIRE ROW based on a single cell you must ANCHOR that single cell's column address with a "$", otherwise Excel will only get the first column correct. Why?

Because Excel is setting your Conditional Format for the SECOND column of your row based on an OFFSET of columns. For the SECOND column, Excel has now moved one column to the RIGHT of your intended rule cell, examined THAT cell, and has correctly formatted column two based on a cell you never intended.

Simply anchor the COLUMN portion of your rule cell's address with "$", and you will be happy

For example: You want any row of your table to highlight red if the last cell of that row does not equal 1.

Select the entire table (but not the headings) "Home" > "Conditional Formatting" > "Manage Rules..." > "New Rule" > "Use a formula to determine which cells to format"

Enter: "=$T3<>1" (no quotes... "T" is the rule cell's column, "3" is its row) Set your formatting Click Apply.

Make sure Excel has not inserted quotes into any part of your formula... if it did, Backspace/Delete them out (no arrow keys please).

Conditional Formatting should be set for the entire table.

NewToo
  • 91
  • 1
  • 2
2

You want to apply a custom formatting rule. The "Applies to" field should be your entire row (If you want to format row 5, put in =$5:$5. The custom formula should be =IF($B$5="X", TRUE, FALSE), shown in the example below.

qJake
  • 16,821
  • 17
  • 83
  • 135
  • So would this work for every row in the worksheet? Additionally, can I just limit it to columns (e.g., A5:G5)? – Josh May 22 '12 at 16:45
  • 1
    Yes, and yes, although you'd have to have a separate rule for each row, I think. Trying to make each row hilight with one rule would get very complicated, at that point I would suggest using a bit of VBScript to achieve what you want. I think if you copy and paste a row with a rule, the rule gets pasted, as well, so you could always try that. – qJake May 22 '12 at 17:13
0

Use RC addressing. So, if I want the background color of Col B to depend upon the value in Col C and apply that from Rows 2 though 20:

Steps:

  1. Select R2C2 to R20C2

  2. Click on Conditional Formatting

  3. Select "Use a formula to determine what cells to format"

  4. Type in the formula: =RC[1] > 25

  5. Create the formatting you want (i.e. background color "yellow")

  6. Applies to: Make sure it says: =R2C2:R20C2

** Note that the "magic" takes place in step 4 ... using RC addressing to look at the value one column to the right of the cell being formatted. In this example, I am checking to see if the value of the cell one column to the right of the cell being formatting contains a value greater than 25 (note that you can put pretty much any formula here that returns a T/F value)

Bill Mac
  • 1
-2

In my case I wanted to compare values in cells of column E with Cells in Column G

Highlight the selection of cells to be checked in column E.

Select Conditional Format: Highlight cell rules Select one of the choices in my case it was greater than. In the left hand field of pop up use =indirect("g"&row()) where g was the row I was comparing against.

Now the row you are formatting will highlight based on if it is greater than the selection in row G

This works for every cell in Column E compared to cell in Column G of the selection you made for column E.

If G2 is greater than E2 it formats

G3 is greater than E3 it formats etc

Naroe
  • 1
  • 1