Share via

I would like to highlight a whole row from conditional formatting based on the one cells that is highlighted in that row.

Anonymous
2024-06-03T19:37:01+00:00

Right now I can highlight one cell in the row I want to highlight entirely. I do not know how to extend this highlight to the whole row where the one cell is highlighted. I have an If statement containing 0 and 1 that is highlighted with the conditionally formatting rule of highlight cell if = to 1. I would like this to be extended to the row containing other information as well like name, number ect. Help would be much appreciated!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-06-03T20:55:51+00:00

    Hi Morgan. I am an Excel user like you.

    I'm afraid that this may be a somewhat vague answer because the details in your question are very vague. I started with this trial setup that is what I picture from your description.

    To format entire rows based on the data 1 or 0 in Column C, highlight all of the cells in all columns that you want to be formatted if they meet the condition. Then enter this formula in the Home ribbon>Conditional Formatting> New Rule>Use a Formula window.

    =$C2=1

    The Row 2 reference in C2 should match the first row of your selected cells. If you start the selection on a different row, make the formula match your first row. Adjust the column reference to match your data. Note that it is the $ sign before the column letter that allows the Conditional Formatting to be applied to all cells across the row while referring to the same cell to be evaluated in every column. Without it Excel adjusts that reference as you move across the row changing the cell that is being evaluated for every column.

    Then set the formatting you want by clicking the format button at the lower right and then OK and OK.

    HOWEVER, if you are only using the 1 and 0 to determine the Conditional Formatting and don't actually need that data in your spreadsheet, it would be better to just use your IF statement in the Conditional Formatting window. Again, note the $ sign to allow the rule to be applied all the way across the row.

    =IF($B2<200,1,0)

    As I said above, I'm just guessing at the possible layout of your data. If this is not close enough to help solve your issue, reply with more information including the formulas you are using and a screenshot of your layout (sensitive information removed).

    50+ people found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-06-03T20:41:05+00:00

    Let's say the 0 / 1 values are in D2:D100.

    Select the entire rows 2 to 100. The active cell in the selection should be in row 2.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =$D2=1

    Click Format...
    Activate the Fill tab.
    Select a highlight color.
    Click OK, then click OK again.

    30+ people found this answer helpful.
    0 comments