Condition Formatting Question

[penmann]

Hi folks, I'm new to the message board and had a question (of course). I'm looking to use Excel to validate an inventory. I have two columns of data, 'Asset Tags' and 'Serial'. I want to have Excel conditionally format a cell in a third column 'Validate' if it appears in 'Assets' or 'Serial' based upon a checkbox status. i.e., if the checkbox is TRUE, the Excel compares the items in 'Validate' column, to 'Assets', if the box status is FALSE, it compares the 'Validate' value to the 'Assets' column. I was looking to do this without VB if possible. Thanks!

 

[helpexce]

I think it is possible.
I suppose you know how to link a cell to the checkbox.
Then you can write choose formula in the conditional formula to do the conditional formula.

I think I can help you.
Please provide sample data.

Br,

 

[witsonjoyet]

Can you give any example or attachment?

 

[penmann]

The sample data is just serial numbers and asset tags, so it is just a random sequence of letters and numbers. Below is an image of what I am talking about. Column I or O will be filled by a client.
Then we will be told which to compare against and fill in our inventory in the merged QR columns, however I want excel to mark duplicates using conditional formatting based on the checkboxes.
So if I click on SER, whatever I type in merged QR will be compared to the I column 'SER' and vice versa. The conditional formatting would mark duplicates, which is what we are looking for since we're trying to verify an inventory.

 

[helpexce]

Try this if it can help.
First you have to link the check box to cell G3 and G2 by right clicking it in form control.

Try below formulas in conditional formula rule.

=IF($G$3,IF(ISNUMBER(MATCH(B2,AST,0)),TRUE),FALSE)
=IF($G$2,IF(ISNUMBER(MATCH(A2,AST,0)),TRUE),FALSE)

AST is name for your merged QR column.
B2 & A2 are for column O & column I.