VBA Mark row with yellow matching criteria

[jakobt]

Want to colour all rows in sheet 1 yellow, which match the following criteria:

The cell in column B should be included in Column F in Sheet 2.

Column B in sheet 1 contains the account for the line item. While Column F in sheet 2 match specific account.

 

[Joe4]

Select column B on Sheet1, and enter the following Conditional Formatting formula:

=COUNTIF(Sheet2!F:F,B1)>0

and choose the yellow formatting color.

 

[jakobt]

Thanks great idea.

I would prefer a VBA for each code, as I need to built several further actions on at a later stage all built into one macro.

 

[Joe4]

You can do that with VBA.
Simply turn on the Macro Recorder and record yourself performing the steps in my previous post manually, and you will have the VBA code you need to do that.

 

[jakobt]

No cannot.
Should have a code like for each cell in column A;
Criteria matching cell in column in cell F, Sheet2.
Mark row with yellow
Next.
End sub


This was the format I was thinking off...

 

[Joe4]

No cannot.
Should have a code like for each cell in column A;
Criteria matching cell in column in cell F, Sheet2.
Mark row with yellow
Next.
End sub


This was the format I was thinking off...

Can you explain why that will not work?
I need to make sure I understand your needs, because different methods have different implications.

For example, Conditional Formatting is dynamic, which means once it is set up, it happens automatically and any changes are reflected IMMEDIATELY!
There are two different VBA methods to do something what you are talking about:
- One is to use automated VBA code that runs automatically as someone manually updates the cells (the disadvantage here is that is that it does not run on already existing data, just newly entered data)
- The other is VBA to be run manually whenever you want (the disadvantage is that it is not automated)