[csit17]

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

 

[Advertisements]

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

[Yisroel]

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

[csit17]

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

[Yisroel]

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

[csit17]

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

[Yisroel]

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: