Conditional formatting

D

Doublecork

New Member
Joined
Sep 8, 2009
Messages
25
[TABLE="width: 854"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]I am trying to figure out how to get all the cells to highlight when I use conditional formatting. I am able to get one Colom to highlight but not any of the cells next to it. This is what I used to get what I have in colom G
=TODAY()-2 =TODAY()-3 to turn them yellow

=TODAY()-3 =TODAY()-4 to turn them red

How do I get A to F to turn the same color as G

<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
What is the condition?

=TODAY()-2 =TODAY()-3

=TODAY()-2 = May / 30
=TODAY()-3 = May / 29

 May / 30 it is not equal to May / 29

Then the result is false

Then it will not highlight anything.
The condition format highlight when the condition is true.
 
Upvote 0
[TABLE="width: 854"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]I run a report to find items that have not been move from a production line. If it is more than two day it need to show in yellow and red if more than three days.



[TABLE="width: 854"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]LINE0011[/TD]
[TD]86629917[/TD]
[TD]2019 WMT DRS PAIN RELIEF PDQ 14PC[/TD]
[TD]9[/TD]
[TD]NAZA597[/TD]
[TD]100000008237002[/TD]
[TD]5/28/2019 15:19[/TD]
[/TR]
[TR]
[TD]LINE0011[/TD]
[TD]86629917[/TD]
[TD]2019 WMT DRS PAIN RELIEF PDQ 14PC[/TD]
[TD]9[/TD]
[TD]NAZA597[/TD]
[TD]100000008226539[/TD]
[TD]5/28/2019 18:02[/TD]
[/TR]
[TR]
[TD]LINE0015[/TD]
[TD]86955849[/TD]
[TD]2019 RITE AID CLARITIN SKNY TWR 32PC[/TD]
[TD]9[/TD]
[TD]NAZA616[/TD]
[TD]100000008940510[/TD]
[TD]5/28/2019 18:11[/TD]
[/TR]
[TR]
[TD]LINE0015[/TD]
[TD]86955849[/TD]
[TD]2019 RITE AID CLARITIN SKNY TWR 32PC[/TD]
[TD]9[/TD]
[TD]NAZA616[/TD]
[TD]100000008940509[/TD]
[TD]5/28/2019 18:17[/TD]
[/TR]
[TR]
[TD]LINE0011[/TD]
[TD]86629917[/TD]
[TD]2019 WMT DRS PAIN RELIEF PDQ 14PC[/TD]
[TD]9[/TD]
[TD]NAZA597[/TD]
[TD]100000008226512[/TD]
[TD]5/28/2019 21:09[/TD]
[/TR]
[TR]
[TD]LINE0009[/TD]
[TD]86593939[/TD]
[TD]2019 WMT ALEVE LAG PDQ 22PC[/TD]
[TD]16[/TD]
[TD]NAZA623[/TD]
[TD]100000008724748[/TD]
[TD]5/29/2019 21:13[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]91[/TD]
[TD]CV019M0B[/TD]
[TD]100000008938969[/TD]
[TD]5/30/2019 7:23[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938963[/TD]
[TD]5/30/2019 8:36[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938968[/TD]
[TD]5/30/2019 8:36[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938967[/TD]
[TD]5/30/2019 9:25[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938966[/TD]
[TD]5/30/2019 10:47[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938965[/TD]
[TD]5/30/2019 11:33[/TD]
[/TR]
[TR]
[TD]LINE0004[/TD]
[TD]86125323[/TD]
[TD]CT SPORT SPRAY SPF50 5.5OZ 3PK[/TD]
[TD]125[/TD]
[TD]CV019M4C[/TD]
[TD]100000008938964[/TD]
[TD]5/30/2019 12:24[/TD]
[/TR]
[TR]
[TD]LINE0002[/TD]
[TD]86312328[/TD]
[TD]Asp Cold Sparkling Original Taef 4s[/TD]
[TD]100[/TD]
[TD]X23R34[/TD]
[TD]100000008226370[/TD]
[TD]5/30/2019 17:37[/TD]
[/TR]
[TR]
[TD]LINE0002[/TD]
[TD]86312328[/TD]
[TD]Asp Cold Sparkling Original Taef 4s[/TD]
[TD]100[/TD]
[TD]X23R34[/TD]
[TD]100000008938995[/TD]
[TD]5/31/2019 7:37[/TD]
[/TR]
[TR]
[TD]LINE0002[/TD]
[TD]86312328[/TD]
[TD]Asp Cold Sparkling Original Taef 4s[/TD]
[TD]100[/TD]
[TD]X23R34[/TD]
[TD]100000008938994[/TD]
[TD]5/31/2019 9:59[/TD]
[/TR]
[TR]
[TD]LINE0003[/TD]
[TD]85719505[/TD]
[TD]CLAR 24HR TAB CLUB 10MG 105ct 1.5DZ[/TD]
[TD]50[/TD]
[TD]NAA7EF8[/TD]
[TD]100000008931388[/TD]
[TD]5/31/2019 10:02[/TD]
[/TR]
[TR]
[TD]LINE0003[/TD]
[TD]85719505[/TD]
[TD]CLAR 24HR TAB CLUB 10MG 105ct 1.5DZ[/TD]
[TD]49[/TD]
[TD]NAA7EF8[/TD]
[TD]100000008931387[/TD]
[TD]5/31/2019 11:19[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Doublecork said:
[TABLE="width: 854"]
<tbody>[TR]
[TD]I run a report to find items that have not been move from a production line. If it is more than two day it need to show in yellow and red if more than three days.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:58.93px;" /><col style="width:59.88px;" /><col style="width:238.57px;" /><col style="width:26.61px;" /><col style="width:68.44px;" /><col style="width:124.51px;" /><col style="width:71.29px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >LINE0011</td><td style="text-align:right; ">86629917</td><td >2019 WMT DRS PAIN RELIEF PDQ 14PC</td><td style="text-align:right; ">9</td><td >NAZA597</td><td style="text-align:right; ">100000008237002.00</td><td style="text-align:right; ">28/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >LINE0011</td><td style="text-align:right; ">86629917</td><td >2019 WMT DRS PAIN RELIEF PDQ 14PC</td><td style="text-align:right; ">9</td><td >NAZA597</td><td style="text-align:right; ">100000008226539.00</td><td style="text-align:right; ">28/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >LINE0015</td><td style="text-align:right; ">86955849</td><td >2019 RITE AID CLARITIN SKNY TWR 32PC</td><td style="text-align:right; ">9</td><td >NAZA616</td><td style="text-align:right; ">100000008940510.00</td><td style="text-align:right; ">28/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >LINE0015</td><td style="text-align:right; ">86955849</td><td >2019 RITE AID CLARITIN SKNY TWR 32PC</td><td style="text-align:right; ">9</td><td >NAZA616</td><td style="text-align:right; ">100000008940509.00</td><td style="text-align:right; ">28/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >LINE0011</td><td style="text-align:right; ">86629917</td><td >2019 WMT DRS PAIN RELIEF PDQ 14PC</td><td style="text-align:right; ">9</td><td >NAZA597</td><td style="text-align:right; ">100000008226512.00</td><td style="text-align:right; ">28/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >LINE0009</td><td style="text-align:right; ">86593939</td><td >2019 WMT ALEVE LAG PDQ 22PC</td><td style="text-align:right; ">16</td><td >NAZA623</td><td style="text-align:right; ">100000008724748.00</td><td style="background-color:#ff0000; text-align:right; ">29/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">91</td><td >CV019M0B</td><td style="text-align:right; ">100000008938969.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938963.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938968.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938967.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938966.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938965.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >LINE0004</td><td style="text-align:right; ">86125323</td><td >CT SPORT SPRAY SPF50 5.5OZ 3PK</td><td style="text-align:right; ">125</td><td >CV019M4C</td><td style="text-align:right; ">100000008938964.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >LINE0002</td><td style="text-align:right; ">86312328</td><td >Asp Cold Sparkling Original Taef 4s</td><td style="text-align:right; ">100</td><td >X23R34</td><td style="text-align:right; ">100000008226370.00</td><td style="background-color:#ffff00; text-align:right; ">30/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >LINE0002</td><td style="text-align:right; ">86312328</td><td >Asp Cold Sparkling Original Taef 4s</td><td style="text-align:right; ">100</td><td >X23R34</td><td style="text-align:right; ">100000008938995.00</td><td style="text-align:right; ">31/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >LINE0002</td><td style="text-align:right; ">86312328</td><td >Asp Cold Sparkling Original Taef 4s</td><td style="text-align:right; ">100</td><td >X23R34</td><td style="text-align:right; ">100000008938994.00</td><td style="text-align:right; ">31/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >LINE0003</td><td style="text-align:right; ">85719505</td><td >CLAR 24HR TAB CLUB 10MG 105ct 1.5DZ</td><td style="text-align:right; ">50</td><td >NAA7EF8</td><td style="text-align:right; ">100000008931388.00</td><td style="text-align:right; ">31/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >LINE0003</td><td style="text-align:right; ">85719505</td><td >CLAR 24HR TAB CLUB 10MG 105ct 1.5DZ</td><td style="text-align:right; ">49</td><td >NAA7EF8</td><td style="text-align:right; ">100000008931387.00</td><td style="text-align:right; ">31/05/2019</td></tr></table>

Formulas on Conditional Formatting:
Red color
=G2=(TODAY()-3)
Applies To:
=$G$2:$G$19

Yellow Color
=G2=(TODAY()-2)
Applies To:
=$G$2:$G$19
 
Upvote 0
Put a $ before the G in your conditional format formula.
 
Last edited:
Upvote 0
ClaireS said:
Put a $ before the G in your conditional format formula.
Click to expand...

Thanks Claire.

It must be like Claire says.

It would be like this:

Formulas on Conditional Formatting:
Red color
=$G2=(TODAY()-3)
Applies To:
=$A$2:$F$19

Yellow Color
=$G2=(TODAY()-2)
Applies To:
=$A$2:$F$19
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with Conditional Formatting
Replies
2
Views
238
Peter_SSs
Peter_SSs
S
  • Solved
Conditional format to highlight future dates
Replies
3
Views
238
Joe4
Joe4
E
  • Question Question
Conditional formatting of blank cell based on date in adjacent cell
Replies
3
Views
374
Joe4
Joe4
R
  • Question Question
Conditional Formatting with Color Gradient
Replies
3
Views
684
myall_blues
myall_blues
D
  • Question Question
Conditional formatting for specific number of cells
Replies
9
Views
348
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top