Conditional Format with formula for each row

SergSlim

New member
Joined
Oct 21, 2013
Messages
2
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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.
 

SergSlim

New member
Joined
Oct 21, 2013
Messages
2
Reaction score
0
Points
0
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.
 
Top