If you have a large dataset to work with, in Microsoft Excel then one of the common things you may be required to do is to highlight a specific cell or set of cells if a particular value/values exist in a different column.
Since conditional formatting in excel helps you to format excel cells based on the value In it, your first instinct may be to use conditional formatting for this kind of work. The focus of conditional formatting is on the cell itself and not a value in another cell.
However, by creating a special conditional rule, you can highlight Excel cells or a range of cells based on whether a value exists on some other columns or not.
Let’s say we want to highlight the names of all accountants in a dataset presented before us. We cannot do that manually as this would take time. How do we fish out these names from a huge data set?
This tutorial is aimed at helping you to deal with situations like this when the need arises.
Let’s get right to it.
How to highlight Excel cells if a specific value exists in another column
Following the practical case given above, the following datasets contain the list of the names of employees in an organization in one column and their positions in another column. If you are tasked to highlight the names of all accountants, how will you do that?
One option is to skim through the data one after the other and select each of them but that will be tedious if there are a lot of them. This method here is the simplest method you can use to achieve that same result.
That is exactly what we are going to do now.
- Select the column where you wish to highlight the cells. In our case, it’s the company positions column as shown above.
- Click on the Home tab and in the Styles group, select conditional formatting.
- In the popup menu, select New Rule and this will launch the New Formatting Rules Dialogue Box.
Click on “Use a formula to determine which cells to format” in the “select a rule type” dialogue box.
In the “Edit the rule description” box, insert [code]=B2=”ACCOUNTANT” [/code] and click on the Format button.
- Select fill tab from the box that opens and choose the color in which you want to highlight the cells.
- Click on OK
- Click on OK
As displayed above, the steps highlighted only the names of the accountants in the list given.
Highlighting an entire row based on value in one column
Assuming you want to highlight the column where the company position is “ACCOUNTANT”.
We can apply the same concept as in the previous example, but instead of applying conditional formatting to one column, we must apply it to the entire data set.
The steps are as follows:
- Choose the entire dataset.
- Select the Home tab.
- Click Conditional Formatting in the Styles group.
- Click the New Rule option from the list of options that appears. The New Formatting Rules dialogue box will be displayed as a result.
- Click on ‘Use a formula to determine which cells to format’ in the ‘Select a Rule type’ options.
- In the field below, enter the following formula: =$B2=”ACCOUNTANT”
- Select the Format option.
- Choose the colour you want to highlight the cells within the Fill tab.
- Click the OK button.
- Click the OK button.
Highlight Cell If Same Value Exists in Another Column Using VBA
You can also use an Excel VBA Macro to highlight a cell if it contains a value from another column. Simply follow these steps:
Step 1: Open your Excel workbook and then select “Visual Basic” from the DEVELOPER tab, or simply press the “ALT+F11” shortcut.
- The visual basic window will appear
- Click insert > Module to create a new module
- Paste the code below into the code window and click the SAVE button
[code] Sub HighlightCellIfValueExistsinAnotherColumn()
Dim ws As Worksheet
Dim x As Integer
Dim Find As Variant
Set ws = Worksheets(“Sheet4”)
For x = 1 To ws.Range(“A” & Rows.Count).End(xlUp).Row
Set Find = ws.Range(“B:B”).Find(What:=ws.Range(“A” & x).Value, LookAt:=xlWhole)
If Not Find Is Nothing Then
If ws.Cells(Find.Row, 6).Value = 0 And ws.Cells(Find.Row, 9).Value = 0 Then
ws.Range(“A” & x).Interior.ColorIndex = 6
- back to the current worksheet, then run the above excel macro. Click Run button.
Results are displayed below
DIFFERENCE BETWEEN (=$B2=”ACCCOUNTANT AND =B2=ACCOUNTANT)
When you select a range of cells and apply conditional formatting to it, Excel will go through each cell in the range and check it against the condition you specified (in our case, the formula =B2=”ACCOUNTANT”).
When it analyzes cell A2, it determines whether the formula returns True or False. As a result, it would check cell B2 to see if the value is equal to ACCOUNTANT or not.
If the formula returns True, the cell in column a will be highlighted in the format specified. And if the formula returns FALSE, nothing happens.
However, I’ve made this a mixed reference by inserting a dollar sign before the column letter B (=$B2=”ACCOUNTANT”). This means that, regardless of which sale is being examined in the row, conditional formatting will always check the cell value in column B to see if it is equal to the US or not.
When conditional formatting is applied to cell A2, it determines whether the value in B2 is in the US or not. Cell B2 is then examined, and it performs the same function.
And now, when conditional formatting is applied to cell A3, it will check the country name in cell B3, and so on.