apply conditional formatting of a cell to all coloumn

%3CLINGO-SUB%20id%3D%22lingo-sub-3373587%22%20slang%3D%22en-US%22%3Eapply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3373587%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20apply%20this%20format%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTODAY()-%24J%244%26gt%3B300%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20the%20whole%20column%20without%20the%20need%20to%20repeat%20this%20formula%20for%20each%20cell%20and%20changing%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20cell%20name%20each%20time%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HassanBosha_0-1652480896300.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371589iA6C39D303DD224F4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HassanBosha_0-1652480896300.png%22%20alt%3D%22HassanBosha_0-1652480896300.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3373587%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3373934%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3373934%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389942%22%20target%3D%22_blank%22%3E%40HassanBosha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20assume%20that%20you%20want%20to%20apply%20the%20conditional%20formatting%20to%20the%20range%20J4%3AJ6%2C%20then%20select%20the%20range%20J4%3AJ6%20and%20then%20make%20a%20new%20rule%20for%20conditional%20formatting%20using%20the%20formula%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTODAY()-%3CSTRONG%3EJ4%3C%2FSTRONG%3E%26gt%3B300%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENotice%20the%20J4%20in%20the%20above%20formula%20which%20must%20be%20the%20first%20cell%20in%20the%20selected%20range%20J4%3AJ6.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3374370%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374370%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20turns%20all%20the%20cells%20into%20red%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20clarify%20what%20should%20I%20do%26nbsp%3B%3C%2FP%3E%3CP%3Ealso%20whenever%20I%20do%20any%20formula%20it%20turns%20the%20blank%20cells%20into%20the%20color%20red%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HassanBosha_0-1652515848441.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371631iF5E2AAAA5EFF5116%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HassanBosha_0-1652515848441.png%22%20alt%3D%22HassanBosha_0-1652515848441.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375074%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20if%20I%20want%20to%20highlight%20the%20cell%20of%20the%20current%20date%20on%20the%20calendar%20in%20the%20column%3CBR%20%2F%3Eand%20make%20all%20the%20above%20dates%20invisible%20till%20I%20show%20them%20%3F%3C%2FP%3E%3CP%3Eother%20meaning%20the%20current%20date%20is%20highlighted%20at%20the%20top%20and%20above%20it%20the%20previous%20dates%20but%20invisible%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HassanBosha_0-1652526145856.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371654iC27CF79FE8E721B9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HassanBosha_0-1652526145856.png%22%20alt%3D%22HassanBosha_0-1652526145856.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375197%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389942%22%20target%3D%22_blank%22%3E%40HassanBosha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20dates%20are%20sorted%20or%20not.%20If%20sorted%2C%20two%20rules%20applied%20to%20J4%3AJ30%20or%20so.%3C%2FP%3E%0A%3CP%3E%3D%24J4%3DTODAY()%20to%20highlight%20current%20date%3C%2FP%3E%0A%3CP%3E%3D%24J4%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375207%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375207%22%20slang%3D%22en-US%22%3ENo%2C%20I%20meant%20the%20dates%20in%20column%20B%3CBR%20%2F%3Eand%20yes%20they%20are%20sorted%20I%20just%20want%20that%20the%20date%20of%20the%20current%20day%20to%20be%20shown%20at%20first%20exactly%20after%20the%20Date%20cell%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375343%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389942%22%20target%3D%22_blank%22%3E%40HassanBosha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20that%2C%20assuming%20your%20dates%20start%20from%20cell%20B5%20then%20select%20the%20date%20range%20and%20make%20a%20new%20rule%20for%20conditional%20formatting%20using%20the%20formula%20given%20below%20and%20click%20on%20Format%20on%20the%20conditional%20formatting%20window%2C%20go%20to%20the%20Number%20Tab%20and%20then%20to%20Custom%20and%20set%20the%20number%20format%20as%20%3CSTRONG%3E%3B%3B%3B%3C%2FSTRONG%3E%20(three%20semi-colons).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DB5%3CTODAY%3E%3C%2FTODAY%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375376%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%20nearly%20what%20i'm%20trying%20to%20do%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HassanBosha_0-1652538318881.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371671iEE1E1E5DC771B5DA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HassanBosha_0-1652538318881.png%22%20alt%3D%22HassanBosha_0-1652538318881.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20the%20current%20date%20at%20the%20top%20followed%20by%20the%20upcoming%20days%3C%2FP%3E%3CP%3Ealso%20trying%20to%20make%20it%20easy%20to%20expand%20all%20of%20the%20whole%20rows%20easily%20like%20toggling%26nbsp%3B%3C%2FP%3E%3CP%3ELike%20one%20click%20it%20expands%20so%20I%20can%20get%20the%20whole%20column%20all%20things%20are%20visible%3C%2FP%3E%3CP%3Eanother%20click%20makes%20the%20rows%20invisible%20until%20the%20row%20of%20the%20current%20date%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375390%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375390%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1389942%22%20target%3D%22_blank%22%3E%40HassanBosha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20in%20conditional%20formatting%20applied%20to%20column%20J%20use%20in%20rule%26nbsp%3Bformula%20references%20on%20values%20in%20column%20B%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375419%22%20slang%3D%22en-US%22%3ERe%3A%20apply%20conditional%20formatting%20of%20a%20cell%20to%20all%20coloumn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375419%22%20slang%3D%22en-US%22%3EI'm%20sorry%20but%20can%20you%20clarify%20more%20or%20show%20me%20the%20steps%3C%2FLINGO-BODY%3E
Occasional Contributor

I want to apply this format 

=TODAY()-$J$4>300 

for the whole column without the need to repeat this formula for each cell and changing 

the cell name each time 

 

HassanBosha_0-1652480896300.png

 

8 Replies

@HassanBosha 

 

Let's assume that you want to apply the conditional formatting to the range J4:J6, then select the range J4:J6 and then make a new rule for conditional formatting using the formula given below...

 

=TODAY()-J4>300

 

Notice the J4 in the above formula which must be the first cell in the selected range J4:J6.

@Subodh_Tiwari_sktneer 

How about if I want to highlight the cell of the current date on the calendar in the column
and make all the above dates invisible till I show them ?

other meaning the current date is highlighted at the top and above it the previous dates but invisible

HassanBosha_0-1652526145856.png

 

@HassanBosha 

It depends dates are sorted or not. If sorted, two rules applied to J4:J30 or so.

=$J4=TODAY() to highlight current date

=$J4<TODAY() and select same background and font colours.

No, I meant the dates in column B
and yes they are sorted I just want that the date of the current day to be shown at first exactly after the Date cell

@HassanBosha 

To do that, assuming your dates start from cell B5 then select the date range and make a new rule for conditional formatting using the formula given below and click on Format on the conditional formatting window, go to the Number Tab and then to Custom and set the number format as ;;; (three semi-colons).

 

=B5<TODAY()

@Subodh_Tiwari_sktneer 

Okay nearly what i'm trying to do

HassanBosha_0-1652538318881.png

I'm trying to make the current date at the top followed by the upcoming days

also trying to make it easy to expand all of the whole rows easily like toggling 

Like one click it expands so I can get the whole column all things are visible

another click makes the rows invisible until the row of the current date

@HassanBosha 

You may use in conditional formatting applied to column J use in rule formula references on values in column B .

I'm sorry but can you clarify more or show me the steps