VBA to highlight certain cells with specific data

[pscofe]

Hi, i'm looking for VBA code to highlight certain cells. My table below, I would like to highlight anything in local_acc_no that is "Paul, Matt, Antonia, Sebastian) and I want to highlight anything in currency that is NOT EUR, GBP or USD.
This table will change daily so some days will be less rows or more. Thanks

local_acc_no	closing_balance	currency	closing_balance_eur	stmt_date
Paul	-740,000.00​	GBP	-873,266.60​	02/07/2020​
Jack	-462,227.10​	NZD	-270,215.65​	02/07/2020​
Chris	-346,275.07​	GBP	-408,635.75​	02/07/2020​
Matt	-286,560.49​	EUR	-286,560.49​	02/07/2020​
Marianne	-16,549.07​	EUR	-16,549.07​	02/07/2020​
Antonia	-7,038.68​	GBP	-8,306.28​	02/07/2020​
Sophia	-4.00​	ISK	-0.03​	02/07/2020​
Sebastian	-0.02​	EUR	-0.02​	02/07/2020​
Suzanne	-0.01​	USD	-0.01​	02/07/2020​

 

[pjmorris]

Why VBA? it would be much simpler to use Conditional Formating?

 

[pjmorris]

For example:
in Column A:A the conditional formatting function would be: =OR($A1="Paul", $A1="Matt", $A1="Antonia", $A1="Sebastian")
in Column C:C it would be: =AND($C1<>"Currency", $C1<>"",$C1<>"GBP",$C1<>"EUR",$C1<>"USD")

HTH

 

[pscofe]

Thanks Peter. How do I apply the rule to only column A? This is what I'm trying to input into conditional formatting (very badly!!) Thanks

 

[pscofe]

Ignore me, being an idiot. Thanks for your help

 

[pjmorris]

thats allowed - I assume you've fixed it, which is brilliant. Thanks for the feedback.

 

[pscofe]

hello again! Actually, i do need it in VBA if that's possible?!?! Thanks

 

[pjmorris]

What will trigger the code running?

 

[pscofe]

I already have a couple of other sheets that are being driven by some coding, it'll all work off one button.

 

[pjmorris]

Ok, I'm in meetings today so won't get to it until later. One thought is whether, when you update the data, you're simply pasting (which overwrites the formatting), it would retain formatting and conditionally formats if you only paste values? Just a thought.