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.
HansVogelaar , first of all thank you for all the help to this challenge.
I think 1 screenshot will help more than a 1000 words.
Like this one. Why would it highlight Red, while value is really NOT <>"Yes"
If you look closely, you'll see that the formula refers to C2 instead of to C3. You should edit the rule and change the formula to =C3<>"Yes"
- HansVogelaar
Hi,
I have somewhat a similar problem as other people posting in this thread, in the sense that I also want to change the colour of a cell based on the content of another cell. I have managed to do this for one entire column, by creating a new rule using a formula to determine which cells to format. The formula reads: ="A1"="Ongoing" and is applied to "$B:$B". My problem is that I want to apply this rule to the entire sheet (that would be all cells from B to U) without having to create a new rule for each column, as I have about 15 colours that I want to implement.
I have tried to go to "Manage rules..." and change "Applies to:" into "$B:$U" or "$B:$2:$U$118". I've also tried to copy the rule in "Manage rules..." and change "$B:$B" to i.e. "$C:$C". As none of these options worked (nothing happened), I tried to mark columns B, C and D when creating the rule so that the new rule would be applied to several columns from start, but the colour change was only made to column B.
Do you have any suggestions on how to solve this? The file is saved as a ".xlsx"-file. HansVogelaar I'm trying to format the D Column to turn green when the whole row from F-K are either complete or N/A. What could I use for the conditional formatting formula? Screenshot attached for reference.
Select D3:D6 (or further down). D3 should be the active cell in the selection.
Use this formula:
=COUNTIF(F3:K3, "Complete")+COUNTIF(F3:K3, "N/A")=6
HansVogelaar I am sorry if I asked a question that you already answered but whatever formula you suggested in the Q&A, it does format the cell as I needed it.
I am trying to create a hiring template and want the candidate's name to appear in Green on the summary page if, on the data page, the candidates salary expectation is entered.
HansVogelaar , thanks for the feedback. That was an easy bug/typo and cristal clear with the screenshot. However. Even with the right formula and correctly selected cells it is not working how I would like it to. Also I know why. The cells in column C are also formula's of the kind ='vlookup'. It seems like conditional format is not working in that case.
How the value appeared in the cell, by formula or manually, that doesn't matter. Conditional formatting triggers format if formula returns TRUE and skips it in all other cases.
On your screenshot values are only Yes or $N/A. Thus formula =C3<>"Yes" never returns TRUE. It returns FALSE or error, #N/A. Thus conditional formatting skips all cells.
You may modify the formula as
=IFERROR( C3<>"yes", TRUE)when it triggers formatting in case of error or if the value is not "yes"
