I’m working with Microsoft Excel and looking for a way to dynamically apply color to a range of cells based on a range input provided in a specific cell (A1). The goal is to have the specified range (e.g., A12:DE45) filled with a certain color when entered into cell A1.
Here’s what I’ve tried so far: Using Conditional Formatting with the INDIRECT function to reference the range specified in A1. Applying a formula within Conditional Formatting to check if the current cell falls within the dynamic range. Ensuring the range entered in A1 is in the correct format without any extra spaces or characters. Despite these attempts, the cells are not being colored as expected. I’m looking for a solution that does not involve VBA or macros.
Here’s an example of what I’ve tried:
=AND(ROW()>=ROW(INDIRECT($A$1)), ROW()<=ROW(INDIRECT($A$1)), COLUMN()>=COLUMN(INDIRECT($A$1)), COLUMN()<=COLUMN(INDIRECT($A$1)))
Also i try something like :
=COUNTIF(INDIRECT($A$1), "@" )>0
This formulas and many other what I tried was used in Conditional Formatting, but it did not yield the desired result.
Question: Is there a formula or method within Excel’s standard features that can achieve this dynamic coloring based on range input into cell? If so, could you provide a detailed explanation or example of how to set it up?
Your formula need a small change within the testing of the upper limits. You need to add to the top/left values the size of the range to get the Bottom/right values.
=AND(ROW()>=ROW(INDIRECT($A$1)), ROW()<=ROW(INDIRECT($A$1))+ROWS(INDIRECT($A$1))-1,
COLUMN()>=COLUMN(INDIRECT($A$1)), COLUMN()<=COLUMN(INDIRECT($A$1))+COLUMNS(INDIRECT($A$1))-1)
This is the revised formula
=(ROW()>=ROW(INDIRECT($A$1)))*
(ROW()<=ROW(INDIRECT($A$1))+ROWS(INDIRECT($A$1))-1)*
(COLUMN()>=COLUMN(INDIRECT($A$1)))*
(COLUMN()<=COLUMN(INDIRECT($A$1))+COLUMNS(INDIRECT($A$1))-1)
AND function calculates all the result array together into one value, this was the reason of the result.