Forum Discussion
How do I make excel change the colour of a cell depending on a different cells date?
- Nov 21, 2022
Let's say you want to highlight a row if column F contains "Closed".
Use the formula
=$F2="Closed"
If you want something different, please provide more detailed information.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
https://www.dropbox.com/s/dug53y5lufw5wqu/Dialog.xlsx?dl=0
I was trying to have each cell in column G format based on the content of the cell of the corresponding row in column E.
- HansVogelaarApr 20, 2023MVP
Thank you. Here is a screenshot of the rules for column G:
There are three problems here:
- The rules apply to G1:G51, but the formulas refer to the cell in row 2.
- The formula refers to $E$2. The $ sign before the row number make this number absolute (not-changing).
- The text values Curio and Orsino have not been enclosed in double quotes ".
To correct this, change the formulas to
=$E1="Curio"
and
=$E1="Orsino"
See the attached workbook.
- JestonHApr 20, 2023Copper ContributorThank you for both the example and the clarifying explanation!
- LauraDaisyApr 20, 2023Copper Contributor
How do I apply this to all cells? I can only get it to change the cell colours in the first row HansVogelaar
- HansVogelaarApr 20, 2023MVP
Select all the cells that you want to format conditionally before you create the rule.
- LauraDaisyApr 20, 2023Copper Contributor
- HansVogelaarApr 20, 2023MVP
This rule will be applied to A2:B3, and the formula refers to C45. Is that really what you intended?
- LauraDaisyApr 20, 2023Copper Contributorno, ive done it before ages ago but I can't remember how to apply it to all cells required.
I would like the cells in column A/B3 to A/B64 to highlight green if the text in column C says Active. - SergeiBaklanApr 20, 2023MVP
Rule formula shall be applied to to top left cell of the range to which you apply the rule. CF iterates the range applying the formula to each cell of the range. Formula applied to each next cell depends on do you use relative (like A1) or absolute (like $A$1) reference, or their combination.
- HansVogelaarApr 20, 2023MVP
Select A3:B64 and make sure that A3 or B3 is the active cell in the selection.
Select Conditional Formatting > Manage Rules...
Delete any rules that are not working correctly.
Then create a new rule of type 'Use a formula to determine which cells to format' with formula
=$C3="Active"
3 is the row number of the active cell; there is no $ before the row number 3 because we want Excel to change it dynamically for rows 4 to 64.
- MrKasifsSep 26, 2023Copper Contributor
Can you make the formula relative ?
I'm looking to indicates if the values in a table are rising , or dropping by color as the number are sometimes very close.
If the Value is Larger or Smaller than the value to the Right...
A B C D E
123 125 122 130 145
I'd like the cell to Turn Red if values are rising - Green if dropping relative to the previous day. ( Cell to the Right )
Of course there are multiple Rows 🙂