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

User · Answer

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

User · Answer

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 lt  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

User · Answer

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    Select R2C2 to R20C2 Click on Conditional Formatting Select  Use a formula to determine what cells to format  Type in the formula    RC 1    25 Create the formatting you want  i e  background color  yellow   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

User · Answer

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

User · Answer

Use the  indirect  function on conditional formatting    Select Conditional Formatting Select New Rule Select  Use a Formula to determine which cells to format  Enter the Formula   INDIRECT  g  amp ROW     X  Enter the Format you want  text color  fill color  etc   Select OK to save the new format Open  Manage Rules  in Conditional Formatting Select  This Worksheet  if you can t see your new rule  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  amp ROW     CAR   then it will format every row that has CAR in the H Column as the format you specified

User · Answer

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

[excel-2010] Conditional formatting, entire row based

Examples related to excel-2010

Examples related to conditional-formatting