Conditional Formating with VLookup and Holidays for different Countries

[silentwolf]

Hi guys,

I got in my workbook two named ranges for two countries and there holidays.

A conditional formating I was able to create in my workbook but I would like to have it more dynamic and therefore within vba.

If range("B2"). value = "Germany" I like to use the named range "Holidays_Germany" to conditional format the dates in wksDates.. beginning at range "B5".
If it is "Austria" then use "Holidays_Austria" as the vlookup.

In the normal conditional formating I got it like this .."=vlookup(A5,Holidays_Germany,1,0)

It works fine but how can you achieve that in vba? To have a vlookup is not the issue but combined with a conditional formating I don't know.

Has someone maybe time to help me with this issue?

Greatly appreciated.

 

[Marcelo Branco]

You can use this formula in Conditional Formatting...

=ISNUMBER(MATCH(A5,IF($B$2="Austria",Holidays_Austria,Holidays_Germany),0))

M.

 

[silentwolf]

Hi,
I will try that too thanks.
But as it is a larger project with vba I would love to have it working within vba.

 

[Marcelo Branco]

To automate in VBA you should use the Worksheet_Change event checking if Target's address is equal to "$B$2".
If so, your code should make a loop, checking if each cell in the range to be formatted is found in the range whose name is contained in B2 (Target) and format accordingly.

M.

 

[silentwolf]

Hi Marcelo,

I will try that thanks again!

A.