Conditional Formatting to Highlight Entire Row

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
Hi, I'm trying to figure out a way to highlight an entire row green if the value in column AY=1020.

Any idea how to use conditional formatting to do this? Or another easier way?

Thanks!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
1 B C D E G
2 765 667 101 829 417
3 12 899 264 495
4 964 195 280 487
5 238 171 966 490
6 138 860 620 496
7 473 267 586 417
8 748 103 463 732
9 286 232 463 79

Conditional Format
Formula: =$E2=$G$2
Applies to: =$B$2:$E$9

This highlights row 7.
 
Last edited:

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: center;;">B</td><td style="text-align: center;;">C</td><td style="text-align: center;;">D</td><td style="text-align: center;;">E</td><td style="text-align: right;;"></td><td style="text-align: center;;">G</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: center;;">765</td><td style="text-align: center;;">667</td><td style="text-align: center;;">101</td><td style="text-align: center;;">829</td><td style="text-align: right;;"></td><td style="text-align: center;;">417</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: center;;">12</td><td style="text-align: center;;">899</td><td style="text-align: center;;">264</td><td style="text-align: center;;">495</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: center;;">964</td><td style="text-align: center;;">195</td><td style="text-align: center;;">280</td><td style="text-align: center;;">487</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: center;;">238</td><td style="text-align: center;;">171</td><td style="text-align: center;;">966</td><td style="text-align: center;;">490</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: center;;">138</td><td style="text-align: center;;">860</td><td style="text-align: center;;">620</td><td style="text-align: center;;">496</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: center;;">473</td><td style="text-align: center;;">267</td><td style="text-align: center;;">586</td><td style="text-align: center;;">417</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: center;;">748</td><td style="text-align: center;;">103</td><td style="text-align: center;;">463</td><td style="text-align: center;;">732</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: center;;">286</td><td style="text-align: center;;">232</td><td style="text-align: center;;">463</td><td style="text-align: center;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">Conditional Format</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">Formula: =$e2=$G$2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">Applies to: =$B$2:$E$9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

This highlights row 7
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
Thanks cnestg8r - I get the gist of this. Which option do I select under Conditional Formatting though? When I go to "Use a formula to determine which cells to format" I only see a box for one formula and not an "applies to" box. What am I missing?
 

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284

ADVERTISEMENT

Use formula and then choose your method of highlighting. You can "Bold" the font, change the font size or color, fill the the background color...
 

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
I go to Conditional formatting -> New rule -> use a formula to determine which cells to format.

In the formula box I type =$AY=1020 (for any row where the value in column AY=1020, I want to highlight the row green.

Format I select fill = green.

This isn't working, but I'm not sure what I'm doing wrong.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the range of cells you want this to work on?
 

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
Fluff is right. In the dialog where you set the formula, make sure "applies to" includes the entire array. Rows and columns.
 

romeumc

New Member
Joined
Feb 19, 2019
Messages
4
I go to Conditional formatting -> New rule -> use a formula to determine which cells to format.

In the formula box I type =$AY=1020 (for any row where the value in column AY=1020, I want to highlight the row green.

Format I select fill = green.

This isn't working, but I'm not sure what I'm doing wrong.


Change the formula to =$AY1=1020
 

cnestg8r

Active Member
Joined
Dec 26, 2005
Messages
284
If there is one header row, the data starts in AY2. Therfore you need $AY2=1020. Note that you still need to include all columns of the matrix in the range.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,983
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top