Sep 07 2018 12:44 PM
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
Sep 07 2018 01:39 PM
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.
Sep 07 2018 02:20 PM
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.
Sep 07 2018 02:24 PM
Thanks,
For some reason, I tried without success.
thks
Sep 07 2018 05:54 PM
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.
Sep 07 2018 07:54 PM - edited Sep 07 2018 07:58 PM
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:
These also will be highlighted!
Hope that helps
Sep 07 2018 08:04 PM
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
Sep 07 2018 08:12 PM
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
Aug 10 2021 11:39 PM