SOLVED

Excel formula to highlight cells above and below based on text

%3CLINGO-SUB%20id%3D%22lingo-sub-1984142%22%20slang%3D%22en-US%22%3EExcel%20formula%20to%20highlight%20cells%20above%20and%20below%20based%20on%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984142%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%20Please%20can%20I%26nbsp%3Bget%20some%20help%20on%20a%20formula%20or%20a%20way%20to%20highlight%20cells%20above%20and%20below%20another%20cell%20which%20contains%20a%20certain%20text.%20for%20example%20I%26nbsp%3Bhave%20a%20cell%20which%20contains%20a%20word%20%22bleep%22%2C%20I%26nbsp%3Bneed%20to%20highlight%20or%20even%20flag%20the%20cell%20directly%20above%20and%20directly%20below%20this%20word.%20Thanks%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1984142%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984244%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20highlight%20cells%20above%20and%20below%20based%20on%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893080%22%20target%3D%22_blank%22%3E%40Athena219%3C%2FA%3E%26nbsp%3BUse%20conditional%20formatting%20like%20in%20the%20attached%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMore%20about%20that%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984269%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20highlight%20cells%20above%20and%20below%20based%20on%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20that's%20perfect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984295%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20highlight%20cells%20above%20and%20below%20based%20on%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984295%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893080%22%20target%3D%22_blank%22%3E%40Athena219%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20could%20be%20possible%20with%20conditional%20formatting%2C%20appreciate%20if%20you%20can%20please%20share%20the%20sample%20file%20with%20your%20data%20and%20highlight%20the%20cells%20manually%20that%20will%20help%20us%20to%20understand%20and%20support%20you%20on%20your%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23999999%22%3E%3CEM%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3BOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1984307%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20highlight%20cells%20above%20and%20below%20based%20on%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1984307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3Battached%20is%20the%20sample.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Please can I get some help on a formula or a way to highlight cells above and below another cell which contains a certain text. for example I have a cell which contains a word "bleep", I need to highlight or even flag the cell directly above and directly below this word. Thanks

7 Replies
Best Response confirmed by Athena219 (Occasional Contributor)
Solution

@Athena219 Use conditional formatting like in the attached example.

 

More about that here:

https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60... 

Hi @Athena219 

 

This could be possible with conditional formatting, appreciate if you can please share the sample file with your data and highlight the cells manually that will help us to understand and support you on your query.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

@Faraz Shaikh attached is the sample. Thanks

Hi @Athena219  

 

Please find attached is solution file, take care of the range selection while making this conditional formatting.

 

Snag_f04556.png

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

@Faraz Shaikh Thank you. This really helped.