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.
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.
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.
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.