SOLVED

Format an entire row.

%3CLINGO-SUB%20id%3D%22lingo-sub-2287195%22%20slang%3D%22en-US%22%3EFormat%20an%20entire%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287195%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20format%20an%20entire%20row%20colour%20based%20on%20any%20date%20being%20entered%20in%20one%20cell%26nbsp%3B%20in%20that%20row%20and%20have%20it%20applied%20to%20the%20whole%20page.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2287195%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2287827%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20an%20entire%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035952%22%20target%3D%22_blank%22%3E%40Rick64%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20haven't%20specified%20any%20details%2C%20so%20I'll%20provide%20a%20random%20example.%20Let's%20say%20you%20want%20to%20highlight%20a%20row%20if%20the%20Sell%20Date%20is%20more%20than%2030%20days%20after%20the%20Purchase%20Date.%3C%2FP%3E%0A%3CP%3ESelect%20the%20entire%20range%20from%20row%202%20down%20to%20the%20last%20used%20row.%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20the%20active%20cell%20in%20the%20selection%20is%20in%20row%202.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%24N2-%24A2%26gt%3B30%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20%24%20signs%20before%20the%20column%20letters%20are%20essential%20here%20-%20the%20%22fix%22%20the%20columns.%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActvate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2289726%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20an%20entire%20row.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20mate%2C%20thanks%20for%20your%20reply%20but%20does%20not%20seem%20to%20work.%20If%20one%20of%20my%20share%20holdings%20gets%20sold%20I%20would%20like%20to%20have%20that%20particular%20row%20change%20colour%20so%20that%20it%20is%20easier%20to%20keep%20track%20of%20which%20shares%20I%20am%20still%20holding.%20Will%20try%20and%20attach%20the%20file.%20You%20can%20see%20where%20I%20have%20coloured%20the%20rows%20manually%2C%20i%20would%20like%20this%20to%20happen%20for%20every%20sold%20share%20if%20possible.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need to format an entire row colour based on any date being entered in one cell  in that row and have it applied to the whole page.

4 Replies

@Rick64 

You haven't specified any details, so I'll provide a random example. Let's say you want to highlight a row if the Sell Date is more than 30 days after the Purchase Date.

Select the entire range from row 2 down to the last used row.

I will assume that the active cell in the selection is in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

 

=$N2-$A2>30

 

The $ signs before the column letters are essential here - the "fix" the columns.

Click Format...

Actvate the Fill tab.

Select a color.

Click OK, then click OK again.

best response confirmed by Rick64 (New Contributor)
Solution

@Hans Vogelaar 

Hi mate, thanks for your reply but does not seem to work. If one of my share holdings gets sold I would like to have that particular row change colour so that it is easier to keep track of which shares I am still holding. Will try and attach the file. You can see where I have coloured the rows manually, i would like this to happen for every sold share if possible.

@Rick64 

So a row should be highlighted if column N has a date?

You'd use the formulka =$N2<>"" in the conditional formatting rule.

See the attached version.

@Hans Vogelaar 

Thanks Hans, much appreciated.