Results 1 to 3 of 3

Thread: Conditional Format with formula for each row

  1. #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?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    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.


  3. #3
    Quote Originally Posted by NBVC View Post
    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.

Posting Permissions

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