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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
277
Peter_SSs
Peter_SSs
S
  • Solved
Conditional format to highlight future dates
Replies
3
Views
265
Joe4
Joe4
S
  • Question Question
Conditional Formatting for a Date
Replies
6
Views
183
Joe4
Joe4
E
  • Question Question
Conditional formatting of blank cell based on date in adjacent cell
Replies
3
Views
404
Joe4
Joe4
R
  • Question Question
Conditional Formatting with Color Gradient
Replies
3
Views
703
myall_blues
myall_blues

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

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