If you're working with large datasets, it can be especially helpful to highlight a cell when another cell in the same row contains a specific word. This can quickly draw your attention to relevant data and make it easier to spot patterns or anomalies. In this article, we’ll walk through the steps you need to take to program Excel to highlight a cell based on a condition in another cell, and how to apply this formatting to an entire column.
The first thing to know is that you don’t need any complex formulas or programming languages like Visual Basic for Applications (VBA) to achieve this effect. Excel’s built-in conditional formatting tool is all you need. Conditional formatting allows you to set rules that change the appearance of a cell, such as altering its background color or font style, depending on the content or conditions within the cell. In this case, we want to use conditional formatting to highlight cells based on a word in another cell on the same row.
Let’s say, for example, that you have a table where Column A contains various product names, and Column B contains their respective status (e.g., "Available," "Out of Stock," or "Discontinued"). If you want to highlight the cells in Column A where the corresponding cell in Column B contains the word “Out of Stock,” you can easily set this up with conditional formatting.
To begin, select the range of cells you want to format. For this example, let’s assume you want to highlight the entire Column A whenever the word “Out of Stock” appears in the corresponding row of Column B. First, select all the cells in Column A that you want to apply the formatting to. You can do this by clicking on the first cell in Column A and dragging your cursor down to the last cell, or by selecting the entire column if necessary.
Once you’ve selected the range, go to the Home tab in the ribbon at the top of Excel and click on the "Conditional Formatting" button. A dropdown menu will appear with various options. From this menu, choose "New Rule," which will allow you to create a custom formatting rule.
In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format." This option will allow you to set a formula based on the content of another cell in the same row. In the formula field, enter the formula that checks if the word "Out of Stock" is in the corresponding cell of Column B. The formula should look like this:
=ISNUMBER(SEARCH("Out of Stock", B1))
What this formula does is search for the words "Out of Stock" in the cell in Column B of the same row. If the condition is true (meaning the word "Out of Stock" is found), the formula will return TRUE, and the conditional formatting will be applied to the cell in Column A.
After you’ve entered the formula, click on the "Format" button to choose the formatting style that will be applied when the condition is met. You can change the font color, cell fill color, or even apply a border around the cell. For example, you might want to change the background color of the cell in Column A to red, indicating that the product is out of stock.
Once you’ve set the formatting options to your liking, click "OK" to apply the rule. The cells in Column A will now be automatically highlighted whenever the corresponding cell in Column B contains the words “Out of Stock.” The conditional formatting will update dynamically, meaning that if the status in Column B changes, the formatting in Column A will adjust accordingly.
Now that you know how to apply the rule to a single column, let’s talk about how to apply the same logic to an entire column. If you want the formatting to apply to all rows in your worksheet, simply adjust the formula to apply to the entire column. In the example above, the formula references cell B1, which is the first row of data. To apply the rule to all rows in the sheet, you need to make sure the formula is relative to each row. This can be done by using the formula:
=ISNUMBER(SEARCH("Out of Stock", $B1))
Notice the dollar sign before the column letter "B." This ensures that the formula always looks at Column B, regardless of which row is being formatted. The row number (1 in this case) will change automatically based on the row being evaluated, so each cell in Column A will be highlighted if its corresponding cell in Column B contains the words "Out of Stock."
Once you have set up the conditional formatting, you can apply it to the entire column or a specific range of rows by adjusting the range you selected in the first step. If you want the rule to apply to all of Column A, you can select the entire column by clicking on the column letter at the top of the worksheet.