Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel 2010 Macro help needed


  • Please log in to reply

#1
csit17

csit17

    Member

  • Member
  • PipPip
  • 27 posts

Hi All

 

I am trying to create a macro in excel which looks at a value greater than 0 in column D and then finds and highlights the matching information in columns A & B

 

e.g. In Column A are Customer Names, In Column B are Account Names, In Column D is the Countif function to find the matches in Column A & B. 

 

Hope I've explained it ok

 

Many thanks in advance

 

Mark

 


  • 0

Advertisements


#2
Yisroel

Yisroel

    GeekU Junior

  • GeekU Junior
  • 637 posts

Let me make sure I understand what you want. If there is a value greater than 0 in column D row 3, then the cells of columns A and B row 3 should got highlighted. And so the same check for every row. 

 

Correct? 

 

If so there is no 'macro' needed. You just gotta make Conditional Formating for columns A and B, to highlight with the formula =D1>0


  • 0

#3
csit17

csit17

    Member

  • Topic Starter
  • Member
  • PipPip
  • 27 posts

Hi Yisroel,

 

No sorry. I have data in columns A & B (Customer Name & Account Name) some of the names match and I have done a Countif in column D which confirms the matches where applicable so I want to highlight the matches in both columns.

 

Hope this is clearer.

 

Kind Regards

Mark


  • 0

#4
Yisroel

Yisroel

    GeekU Junior

  • GeekU Junior
  • 637 posts

I still don't understand. Maybe a screenshot of a sheet like yours, and the formula you used in column D, would help.


  • 0

#5
csit17

csit17

    Member

  • Topic Starter
  • Member
  • PipPip
  • 27 posts

Hi Yisroel.

 

Apologies I cant get a screengrab as im on a restricted network drive. I'll try and type it out and hope its clearer LOL

 

Column A           Column B           Column C            Column D

ian                      tony                    01424                  =countif(A:A,B2)

stewart               ian                      0123                    =countif(A:A,B3)

james                 harry                   0321                    =countif(A:A,B4)

tony                    jerry                   789                       =countif(A:A,B5)  

 

So based on the above cell D2 the countif formula would show 1 as tony appears in both column B and Column A. So I want to be to run a macro where there is a value greater than 0 in Column D and highlight the relevent matches in columns B & A

 

Thanks

Mark


  • 0

#6
Yisroel

Yisroel

    GeekU Junior

  • GeekU Junior
  • 637 posts

Still no macro needed, do it via conditional formatting, set two rules:

  • For column A do conditional formatting and set the rule for 
    =COUNTIF(B:B,A1)
  • Then for column B use the rule 
    =$D1>0

This should be good for your case, resulting in a highlighting like this:

XgcXlYv.png


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP