SOLVED

Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2362500%22%20slang%3D%22en-US%22%3EConditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362500%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20trying%20to%20highlight%20cells%20based%20on%20what%20column%20B%20in%20my%20spreadsheet%20says%2C%20and%20also%20whether%20or%20not%20a%20cell%20is%20blank.%20So%20Column%20B%20has%20insurance%20types%2C%20and%20I%20need%20to%20highlight%20the%20smaller%20cells%20in%20those%20rows%20for%20%22Med%20A%22%2C%20but%20stop%20highlighting%20them%20once%20a%20cell%20is%20filled.%20I%20don't%20have%20any%20formulas%20in%20the%20smaller%20cells%20to%20make%20them%20not%20empty%2C%20they%20are%20completley%20blank%2C%20so%20I%20am%20not%20sure%20how%20to%20go%20about%20adding%20the%20extra%20%22cell%20is%20blank%22%20parameter.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Capture.JPG%22%20style%3D%22width%3A%20853px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281332i2B9048FEAF767EDA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Capture.JPG%22%20alt%3D%22Capture.JPG%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-2362500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2362551%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2362551%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1056260%22%20target%3D%22_blank%22%3E%40emilyfay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20begin%20in%20row%202.%3C%2FP%3E%0A%3CP%3ESelect%20E2%3AE100%20or%20however%20far%20down%20you%20want.%3C%2FP%3E%0A%3CP%3EE2%20should%20be%20the%20active%20cell%20in%20the%20selection.%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%3DAND(%24B2%3D%22Med%20A%22%2CE2%26lt%3B%26gt%3B%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3EYou%20can%20now%20use%20the%20Format%20Painter%20to%20copy%20the%20formatting%2C%20including%20the%20conditional%20formatting%20rule%2C%20from%20E2%3AE100%20to%20G2%3AG100%2C%20I2%3AI100%20etc.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I am trying to highlight cells based on what column B in my spreadsheet says, and also whether or not a cell is blank. So Column B has insurance types, and I need to highlight the smaller cells in those rows for "Med A", but stop highlighting them once a cell is filled. I don't have any formulas in the smaller cells to make them not empty, they are completley blank, so I am not sure how to go about adding the extra "cell is blank" parameter.

Capture.JPG

 

2 Replies
best response confirmed by emilyfay (New Contributor)
Solution

@emilyfay 

Let's say the data begin in row 2.

Select E2:E100 or however far down you want.

E2 should be the active cell in the selection.

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

 

=AND($B2="Med A",E2<>"")

 

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

You can now use the Format Painter to copy the formatting, including the conditional formatting rule, from E2:E100 to G2:G100, I2:I100 etc.

 

Thank you! For some reason my brain didn't tell me to put the E2 in there, I was just putting the <>"" thinking it would pick up the cell on its own.