Forum Discussion
conditional formatting based on content of another cell
Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell.
On the Home tab of the ribbon. select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise.
In your example, let's say A2 is the active cell in the selection.
Use the following formula:
=B2<>""
Click Format...
Activate the Fill tab.
Specify the desired color (green in your example).
Click OK, then click OK again.
Hi to all,
Can I share my question/problem regarding "formatting cells" as well ?
I am not sure if my approach is identical as all the previous cases…
Here it is :
I have an excel sheet keeping track of stocks that goes down for about 600 different lotnrs (= rows).
In row A, I have headers that show the content of each column with various data of each lotnr. This row is protected by "Freezing panes", to always see the various headers...
In 1 column, the balance of stock is shown of the lotnrs. !!
If a stock goes down under zero, it means too much stock of 1 lot has been used, has been deducted.
I now wish to highlight only 1 cell in row A, the one that remains visible all the time, to show that a certain lotnr. has gone too far. In other words, I should be warned on top of the sheet at all times !
Let us say : cell AB1 is to be highlighted (= formatted on condition in red) –
and column AA is the one with the balance of stocks of all lotnrs. Starting from AA2 until AA600.
The formula has to look up/check all cells in column AA in the given range, if there appears a stock below zero.
How can I do that ?
Many thanks for sharing the insight with me.
Patrick
Select AB1.
On the home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=COUNTIF(AA2:AA600, "<0")
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.
Can you explain more clearly and in detail what you want? Thanks in advance.
HansVogelaar Hi Hans,
thanks for the info !
One extra question on the COUNTIF formula : normally, it counts the number of matches in a range for a specific request and shows the total number of matches. Does it have a different or additional function, combined with the conditional formatting ?
Thanks, Patrick
That depends on your regional settings: if you use comma as decimal separator, you need to use semi-colon between the arguments of a function.
Unless the original question explicitly specifies otherwise, replies in this forum use the USA/UK setting: point as decimal separator and comma as argument separator.
HansVogelaar : Hi, thanks, it works !
One remark : I had to replace your comma into a semi-colon to have the formula work right.
Thanks again !
P.