Forum Discussion
Conditional formating on range of cell
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 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 figuresHi Rafael,
If you want to apply a conditional formatting rule to a range of cells, go to:
Home >> Styles >> Conditional Formatting >> Manage RulesSelect the rule you want, then expand the range as you want by adjusting the Applies to box.
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 RulesSelect the rule you want, then expand the range as you want by adjusting the Applies to box.
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 https://support.office.com/en-us/article/use-formulas-with-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f.
Hope that helps
Thanks,
For some reason, I tried without success.
thks
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.
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
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.
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.
Please find the attached file.
Regards
