Conditional formating on range of cell

%3CLINGO-SUB%20id%3D%22lingo-sub-245693%22%20slang%3D%22en-US%22%3EConditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-245693%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20need%20to%20apply%20a%20conditional%20format%20to%20a%20range.%20I%20unsuccessfully%20tried%20the%20following%3A%3C%2FP%3E%3CP%3E1.%20Removing%20%24%20from%20range%20but%20Excel%20put%20it%20back%20when%20%5BOK%5D%20is%20pressed.%3C%2FP%3E%3CP%3E2.%20Copy%20formatting%20through%20%5B%20Format%20Painter%20%5D%2C%20not%20success%3C%2FP%3E%3CP%3E3.%20Selecting%20the%20range%20and%20use%20the%20following%20formula%3A%20%5B%20%3DISNUMBER(SEARCH(%22Rendering%22%2CB4))%3DTRUE%20%5D%2C%20not%20success.%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-245693%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-247226%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-247226%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Debora%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20cannot%20remove%20the%20dollar%20signs%20from%20the%20%3CSTRONG%3EApply%20to%3C%2FSTRONG%3E%20box%2C%20they%20required!%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20range%20is%20dynamic%20even%20with%20these%20dollar%20signs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20example%20below%2C%20the%20rule%20is%20applied%20to%20the%20range%20B2%3AB15.%3C%2FP%3E%3CP%3ETry%20to%20type%20Rendering%20in%20cell%20B16%2C%20and%20you%20will%20notice%20how%20the%20rule%20will%20expand.%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules%202.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F47459i2C9B07AC33D29DAF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%20-%20Manage%20Rules%202.png%22%20alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules%202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20file.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-247181%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-247181%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20most%20cases%2C%20you%20don't%20have%20to%20touch%20the%20%3CSTRONG%3EApply%20to%20%3C%2FSTRONG%3Ebox!%3C%2FP%3E%3CP%3EJust%20select%20the%20targeted%20range%20before%20you%20apply%20the%20conditional%20formatting%20and%20it%20will%20be%20applied%20to%20this%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fuse-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-247128%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-247128%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rafael%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20formula%2C%20you%20have%20to%20replace%20the%20cell%20B4%20with%20the%20active%20cell%20in%20the%20selected%20range.%3C%2FP%3E%3CP%3EThe%20active%20cell%20in%20the%20selected%20range%20below%20is%20cell%20B2%2C%20so%20I%20pass%20it%20in%20the%20formula.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting%20-%20Formula.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F47359i121E8426F341F1EE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%20-%20Formula.png%22%20alt%3D%22Conditional%20Formatting%20-%20Formula.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENOTE%3A%3C%2FSTRONG%3E%20No%20need%20for%20%3CSTRONG%3E%3D%20TRUE%3C%2FSTRONG%3E%20in%20the%20formula%20because%20the%20result%20of%20the%20formula%20is%20either%20TRUE%20or%20FALSE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20strip%20the%20formula%20from%20the%20ISNUMBER%20function%20to%20make%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DSEARCH(%22Rendering%22%2CB2)%3C%2FPRE%3E%3CP%3EThis%20is%20because%20SEARCH%20returns%20the%20starting%20position%20number%20of%20Rendering%20in%20each%20cell%20in%20the%20selected%20range%20and%20this%20number%20considered%20always%20as%20TRUE.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20to%20simplify%20the%20formula%20as%20much%20as%20possible%2C%20you%20can%20even%20remove%20SEARCH%20function%20to%20end%20with%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DB2%3D%22Rendering%22%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20last%20formula%20is%20what%20you%20need%20because%20with%20SEARCH%20if%20you%20have%20texts%20like%20this%3A%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20Rendering%3C%2FLI%3E%3CLI%3E3D%20rendering%3C%2FLI%3E%3C%2FUL%3E%3CP%3EThese%20also%20will%20be%20highlighted!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-246512%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-246512%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20want%20to%20apply%20conditional%20formatting%20to%20a%20range%20of%20cells%20IF%20one%20cell%20%3D%20a%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%20%3DIF%20A1%3AA500%20%3D%20%22S%22%20%26nbsp%3B%20then%20I%20wish%20to%20shade%20the%20cells%20of%20that%20row%2C%20A1%3AL500%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20to%20be%20able%20to%20in%20the%20old%20Excel!%26nbsp%3B%20%3A'(%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHi%20Rafael%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20apply%20a%20conditional%20formatting%20rule%20to%20a%20range%20of%20cells%2C%20go%20to%3A%3CBR%20%2F%3EHome%20%26gt%3B%26gt%3B%20Styles%20%26gt%3B%26gt%3B%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20Manage%20Rules%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20rule%20you%20want%2C%20then%20expand%20the%20range%20as%20you%20want%20by%20adjusting%20the%20Applies%20to%20box.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20style%3D%22width%3A%20980px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F46897i45A384267F9D592E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-246139%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-246139%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%2C%20I%20tried%20without%20success.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-246137%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-246137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CEM%3EI%20have%20had%20the%20same%20problem%3B%20Excel%20automatically%20adds%20%24%20to%20the%20range%20in%20the%20Applies%20To%20box%2C%20even%20if%20I%20want%20it%20to%20stay%20dynamic.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-246003%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-246003%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Rafael%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20apply%20a%20conditional%20formatting%20rule%20to%20a%20range%20of%20cells%2C%20go%20to%3A%3CBR%20%2F%3EHome%20%26gt%3B%26gt%3B%20Styles%20%26gt%3B%26gt%3B%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20Manage%20Rules%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20rule%20you%20want%2C%20then%20expand%20the%20range%20as%20you%20want%20by%20adjusting%20the%20Applies%20to%20box.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20style%3D%22width%3A%20980px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F46897i45A384267F9D592E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20alt%3D%22Conditional%20Formatting%20-%20Manage%20Rules.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2634924%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formating%20on%20range%20of%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2634924%22%20slang%3D%22en-US%22%3E%3CBR%20%2F%3EHi%20Sir%2C%20I%20have%20some%20issue%20in%20making%20the%20if%20condition%20kindly%20guide%20in%20this%20cause%2C%20I%20have%20different%20numeric%20data%20in%202%20cells.%20and%20I%20want%20to%20minus%20the%20values%20with%20each%20other%20but%20I%20want%20the%20formula%20auto%20take%20the%20big%20figure%20and%20minus%20the%20values%2C%20because%20in%20the%20result%20always%20comes%20the%20neutral%20vales%20not%20comes%20in%20negative%20figures%3C%2FLINGO-BODY%3E
New Contributor

Hi, I need to apply a conditional format to a range. I unsuccessfully tried the following:

1. Removing $ from range but Excel put it back when [OK] is pressed.

2. Copy formatting through [ Format Painter ], not success

3. Selecting the range and use the following formula: [ =ISNUMBER(SEARCH("Rendering",B4))=TRUE ], not success.

Please help

8 Replies

Hi Rafael,

 

If you want to apply a conditional formatting rule to a range of cells, go to:
Home >> Styles >> Conditional Formatting >> Manage Rules

 

Select the rule you want, then expand the range as you want by adjusting the Applies to box.

Conditional Formatting - Manage Rules.png

 

 

I have had the same problem; Excel automatically adds $ to the range in the Applies To box, even if I want it to stay dynamic.

Thanks, 

 

For some reason, I tried without success. 

 

thks

Hi

I want to apply conditional formatting to a range of cells IF one cell = a value.

 

Example:  =IF A1:A500 = "S"   then I wish to shade the cells of that row, A1:L500

 

I used to be able to in the old Excel!  :'(


@Haytham Amairah wrote:

Hi Rafael,

 

If you want to apply a conditional formatting rule to a range of cells, go to:
Home >> Styles >> Conditional Formatting >> Manage Rules

 

Select the rule you want, then expand the range as you want by adjusting the Applies to box.

Conditional Formatting - Manage Rules.png

 

 


 

Hi Rafael,

 

In the formula, you have to replace the cell B4 with the active cell in the selected range.

The active cell in the selected range below is cell B2, so I pass it in the formula.

Conditional Formatting - Formula.png

 

NOTE: No need for = TRUE in the formula because the result of the formula is either TRUE or FALSE.

 

You can also strip the formula from the ISNUMBER function to make as follows:

=SEARCH("Rendering",B2)

This is because SEARCH returns the starting position number of Rendering in each cell in the selected range and this number considered always as TRUE.

 

Also, to simplify the formula as much as possible, you can even remove SEARCH function to end with this formula:

=B2="Rendering"

 

I think the last formula is what you need because with SEARCH if you have texts like this:

  • The Rendering
  • 3D rendering

These also will be highlighted!

 

Hope that helps

Hi,

 

In most cases, you don't have to touch the Apply to box!

Just select the targeted range before you apply the conditional formatting and it will be applied to this range.

 

Please check out this link.

 

Hope that helps

Hi Debora,

 

You cannot remove the dollar signs from the Apply to box, they required!

Please note that the range is dynamic even with these dollar signs.

 

In the example below, the rule is applied to the range B2:B15.

Try to type Rendering in cell B16, and you will notice how the rule will expand. Conditional Formatting - Manage Rules 2.png

 

Please find the attached file.

Regards


Hi Sir, I have some issue in making the if condition kindly guide in this cause, I have different numeric data in 2 cells. and I want to minus the values with each other but I want the formula auto take the big figure and minus the values, because in the result always comes the neutral vales not comes in negative figures