1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Conditional Format only highlights first occurence

Discussion in 'Business Applications' started by mariaa33, Dec 4, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I am using conditional formatting to look up from list and highlight occurrences in column I. I am using
    =IF(ISERROR(VLOOKUP($I2, Sheet1!$A:$A, 1, 0)), 0, 1) or
    =MATCH($I2,Sheet1!A:A,0)>0 . Both only highlight the first instance in the column. How would I get it to highlight the first and second instances for each on the list. I tried with offset but not good with offset. Any help is appreciated.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    you could use a countif

    =countif(A:A, $i2)>0
     
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    The above doesn't work because the list is on a separate WS. This works but only if I have clicked on I1 and then the I column. =COUNTIF(Sheet1!A:A, $I1)>0. If I try i2 then it highlights the the first cell and the unrelated one above it....don't know why.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    The conditional formatting range needs to match the formula
    so if you select A:A to highlight - then you need to start at I1
    if you seelct A2:A200 then you use I2

    Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
     
  5. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    No need to upload...it is working fine.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1161648

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice