Verifying data in 2 cells are valid with each other

Question

Verifying data in 2 cells are valid with each other

Scott Grinder 0

We enter data in 2 cells. When you enter data (number) in A1, the data you enter in A2 (alpha-numeric) must be a specific value. For example, if I enter 99 in cell A1, the data entered in A2 MUST be JC. If I enter anything besides JC in A2 when 99 is entered in A1, I'd like to see it highlighted or some type of message show or pop-up. I'd like to see the cell highlight in color as a preference.

Chris-D 1,595 Reputation points Independent Advisor

2026-09-14T15:00:00.4466667+00:00

Hi Scott,

Have you had a chance to review the information provided? Any updates would be greatly appreciated.

2 answers

Your answer

Chris-D 1,595 Reputation points Independent Advisor

2026-09-14T15:00:00.4466667+00:00

Hi Scott,

Have you had a chance to review the information provided? Any updates would be greatly appreciated.

Answer 1

Use either data validation for an error message, or conditional formatting for a color highlight.

For the exact rule described:

A1 = 99
A2 must equal JC

A good way to highlight A2 when it is wrong is conditional formatting.

Select A2.
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Enter this formula:

=AND($A$1=99,$A$2<>"JC")

Click Format.
Choose a fill color.
Click OK.

Result:

If A1 contains 99 and A2 is anything other than JC, cell A2 is highlighted.
If A1 is not 99, the rule does not apply.

If a blank A2 should also be treated as invalid when A1 is 99, the same formula already does that.

If a pop-up style warning is preferred instead of just color, use Data Validation on A2 with a custom formula:

=OR($A$1<>99,$A$2="JC")

That allows either:

A1 is not 99, or
A2 is JC

If A1 is 99 and A2 is not JC, Excel can show an error alert.

Answer 2

Hi Scott,

I hope you’ve been well.

Based on your description, you can use Conditional Formatting in Excel to highlight cell A2 when its value does not match the value required by A1.

For example, if A1 contains 99, and A2 must contain JC, you can try the steps below:

Select cell A2.
Go to Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter the following formula: =AND($A$1=99,$A$2<>"JC")
Select Format, choose your preferred fill color, and then select OK.

After the rule is applied, A2 will be highlighted whenever A1 contains 99 and A2 contains anything other than JC.

If you also want Excel to prevent users from entering an incorrect value and display an error message, you can additionally configure Data Validation for cell A2.

For references:

I hope this information is helpful. Should you have any further questions or need additional assistance, please feel free to share them in the comment below. I'm very happy to help.

If the answer is helpful, please click 'Yes' and kindly upvote it.

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.