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.

For each matching cell in column A, evaluate Column B value

Discussion in 'Business Applications' started by mariaa33, Nov 23, 2015.

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

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Not sure exactly how to word what I am trying to accomplish. for each matching cell in Column A, evaluate the values in Column B for matching rows. In the example spreadsheet, I have highlighted the ones I want vba to evaluate. The column can also be as large as 5000 rows at times. So for Dayton, at least one % is 0 and one is more than 1%. In that case I want the 0% highlighted. Same for Los Angeles and Annapolis which has 4 matching rows. However, I don't want Sacramento and Mobile 0% value highlighted because the value(s) are all below 1% each. I don't even know where to start on coding this. Any help is greatly appreciated.
     

    Attached Files:

  2. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    I'm attaching a copy of your file and I've put a formula in an adjacent column to see if it helps you clarify what you want. The way I read it is that you need at least one of the matching rows (per City) to be over 1%, assuming you achieve that threshold, you would highlight one (or more?) rows for that city that are 0%. As you can see, in the attached, you end up with two rows highlighted blue for Dayton and LA. Annapolis doesn't have a 0% value, so I haven't highlighted it.

    Reading your instructions, it struck me that rather than absolute zero values, you might be looking for values of less than 1%, in which case I have used a further formula in column D (orange vertical stripe) to test against that outcome. Rows highlighted with an orange vertical stripe (including those two coloured blue) indicate a row where the value is less than 1%, but there are others for that city over 1%

    In terms of coding, you'd have to apply the same sort of logic, but I can see it being slower as you'd have to run down the columns of data (most likely in more than one pass), to test for the presence of the 1%+ within the array of values for each city. You can use countifs as a worksheet function in VBA ... (Application.Worksheetfunction.Countifs)
     

    Attached Files:

    Last edited: Nov 24, 2015
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Is there a way to put this into vba and actually highlight column B instead of putting "HIGHLIGHT" AND "DON'T HIGHLIGHT"?

    Code:
    =IF(AND(AND(COUNTIFS($A:$A,$A2,$B:$B,">="&0.01)>=1,B2=0),(AND(COUNTIFS($A:$A,$A2,$B:$B,">="&0.01)>=1,B2<0.01))),"HIGHLIGHT","DON'T HIGHLIGHT")
    
     
  4. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I tried this but get type mismatch

    Code:
    Sub Highlt()
      With ActiveSheet.UsedRange
      .Cells(2, 2).Select
      .FormatConditions.Add Type:=xlExpression, Formula1:="AND(AND(COUNTIFS($A:$A,$A2,$B:$B," >= "0.01)>=1,B2=0),(AND(COUNTIFS($A:$A,$A2,$B:$B," >= "&0.01)>=1,B2<0.01))"""
      .FormatConditions(1).Interior.ColorIndex = 40
      End With
    End Sub
    
     
    Last edited: Nov 24, 2015
  5. CodeLexicon

    CodeLexicon

    Joined:
    Oct 15, 2013
    Messages:
    503
    Yes, I only did that to demonstrate/highlight the formulas. I can't test your VBA at the moment as I'm just using my phone.
     
  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...
Similar Threads - each matching cell
  1. AndrewBlaikie
    Replies:
    0
    Views:
    386
Thread Status:
Not open for further replies.

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

  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