# Thread: Conditional Format with formula for each row

1. ## Conditional Format with formula for each row

Register for a FREE account, and/
or Log in to avoid these ads!

Hello,

I have a file with two columns
Column A has a list of equipment names, for example: EQ1, EQ2, EQ3, etc.
Column B has serial numbers of equipment from column A.

I need to create a complex conditional formatting, which will check:
If A1=EQ1, and B1<>"HCC*" then make it red,
OR
if A1=EQ2, and B1<>"ABC*" then make it red,
OR
if A1=EQ3, and B1<>OR("CDE*","FGE") then make it red.

And then same thing for A2, B2 cells.

I have thousands of rows in both columns.

I have to check whether selected equipment name corresponds with entered serial name pattern.
I don't know how to do it for each row separately.

I thought of something like:
1st condition: =AND(A1="EQ1",LEFT(B1,3)<>"HCC")
2nd condition: =AND(A1="EQ2",LEFT(B1,3)<>"ABC")
...

But how do I apply this conditional formatting to each row, so that it will compare A2 with B2, A3 with B3, and so on, instead of comparing A1 with B1 in all of the rows?  Reply With Quote

2. If you preselect from A1 to bottom cell in column B, then you can apply the conditional format as:

=OR(AND(A1="EQ1",LEFT(B1,3)<>"HCC"),AND(A1="EQ2",LEFT(B1,3)<>"ABC"),AND(A1="EQ3",OR(LEFT(B1,3)<>"CDE",LEFT(B1,3)<>"FGE")))

The A1 and B1 will automatically adjust to the relative rows below.  Reply With Quote

3. Originally Posted by NBVC If you preselect from A1 to bottom cell in column B, then you can apply the conditional format as:

=OR(AND(A1="EQ1",LEFT(B1,3)<>"HCC"),AND(A1="EQ2",LEFT(B1,3)<>"ABC"),AND(A1="EQ3",OR(LEFT(B1,3)<>"CDE",LEFT(B1,3)<>"FGE")))

The A1 and B1 will automatically adjust to the relative rows below.
Thank you.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•